schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_06a_list_of_stems, for use in figuring out the diff in _plots_06_list_of_plots_with_stem_measurements
schemas/vegbien.sql: plot: removed explicit column lists added in the autorename of plot.location_id->plot_id
schemas/vegbien.sql: plot: renamed pkey to plot_id. note that the field is autorenamed in all validation views which use it.
schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location
bugfix: schemas/vegbien.sql: locationevent: added missing fkey on place_visit_id
bugfix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: only include stemobservation records which have actual stem IDs, not merely stem-related measurements (DBH, etc.)
bugfix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: LEFT JOIN to project instead of inner joining, to get Postgres to use the right query plan. this is the last change needed to make query #6 runnable.
bugfix: schemas/vegbien.sql: rematerialize_out_view(): run all queries with `SET enable_seqscan = off` to avoid slow query plans. this fixes _plots_06_list_of_plots_with_stem_measurements and significantly speeds up _plots_10_count_of_individuals_per_plot_in_each_project (and possibly others).
schemas/vegbien.sql: locationevent: documented `CREATE INDEX locationevent_place_visit_id` runtime (3 min)
fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries
bugfix: schemas/vegbien.sql: source_by_shortname(): documented that in some cases, it is actually a bad idea to use a nested SELECT, because this will prevent Postgres from using an index scan (causing an equally bad slowdown as not inlining in cases where a nested SELECT is required).
schemas/vegbien.sql: locationevent: documented `VACUUM ANALYZE` runtime (20 min)
schemas/vegbien.sql: location: documented `CREATE INDEX plot_source_id` runtime (5 min)
fix: schemas/vegbien.sql: location: added plot_source_id index to provide the equivalent of the location.source_id index for outer plots. this will help Postgres choose the right query plans in queries involving outer plots.
schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table
schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table
bugfix: schemas/vegbien.sql: _plots_10a_aggregate_observation_individual_counts: need to use taxonoccurrence.sourceaccessioncode, not aggregateoccurrence.sourceaccessioncode, because aggregateoccurrence.sourceaccessioncode is not populated
schemas/vegbien.sql: public_validations schema comment: documented how to remove a validations query so its columns can be changed (use public_validations.rm_query_view())
schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_10a_aggregate_observation_individual_counts, for use in debugging diffs in _plots_10_count_of_individuals_per_plot_in_each_proj
schemas/vegbien.sql: locationevent: documented `UPDATE locationevent SET place_visit_id = place_visit_id` runtime (>~1 h)
fix: schemas/vegbien.sql: _plots_10_count_of_individuals_per_plot_in_each_project: fixed to handle subplots properly, using new locationevent.place_visit_id
schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.
schemas/vegbien.sql: locationevent: added locationevent_set_place_visit_id() trigger, analogous to location_set_plot_location_id() but for the locationevent nesting hierarchy. this is needed by the SALVIAS validation queries.
fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger
bugfix: schemas/vegbien.sql: public_validations.query_view_relation_max_prefix_len(): added values__ prefix
schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries
schemas/vegbien.sql: public_validations: auto-added values_() functions for all queries
schemas/vegbien.sql: public_validations: auto-added keys() functions for all queries
schemas/vegbien.sql: synced with DB, which reorders ~type tables
bugfix: schemas/vegbien.sql: public_validations: schema comment: also need to re-create keys() functions after running public_validations.rm_all_queries()
bugfix: schemas/vegbien.sql: added back keys() matchup functions, which get cascadingly deleted when the queries are re-created to change column names, etc. note that this causes the associated ~type tables to sort before them.
schemas/vegbien.sql: added new ~type tables
schemas/vegbien.sql: rm_all_queries(schema): documented this function's unexpected behavior when schema = public_validations (it removes in all schemas, not just public_validations). this is because this uses rm_query_view().
fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries
schemas/vegbien.sql: rm_query_group(): support removing all validations as well as just those with a particular group prefix (and renamed to rm_output_queries())
fix: schemas/vegbien.sql: public_validations: schema comment: removed incorrect "" in `SET search_path = "public_validations", pg_catalog;` now that they are no longer auto-added
schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match
fix: schemas/vegbien.sql: added back traits* ~type tables
fix: schemas/vegbien.sql: plots* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)
fix: schemas/vegbien.sql: rm_all_queries(): all-schemas mode: also remove queries that are only present on the input side, so that the input side can also be re-created from the DDL file
fix: schemas/vegbien.sql: rm_all_queries(): documented that this actually does remove queries in all schemas when schema is NULL, because rm_query_view(public_validations.view) searches all schemas
schemas/vegbien.sql: top_plot view: renamed to plot, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)
schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)
schemas/vegbien.sql: source_by_shortname(): documented exact time that the slow query ran for (5013s = ~1.5 hours)
schemas/vegbien.sql: public_validations: schema comment: documented how to sync the queries with schemas/vegbien.sql
schemas/vegbien.sql: public_validations.rm_all_queries(): when called with no args, operate on public_validations instead of all schemas, since this is the more common use case (replacing just the output queries, not both input and output)
schemas/vegbien.sql: rm_query_group(): documented that this only removes validations output queries (in public_validations), as it is designed for use in updating all output queries at once from the testing DB
bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .
schemas/vegbien.sql: source_by_shortname(): documented that it must be run as a nested SELECT, because otherwise Postgres will not inline it. not inlining causes the query to be run for each row in a table of potentially millions, and creates a significant slowdown (eg. >1.5 hours for SALVIAS._plots_06_list_of_plots_with_stem_measurements)
fix: schemas/vegbien.sql: source_by_shortname(): needs to be declared STABLE so it can be constant-folded and only run once per query. (it had previously been VOLATILE because of constraints imposed on functions that run `SET LOCAL search_path`.)
fix: schemas/vegbien.sql: location: added index on top_plot
fix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: changed columns to match input query
schemas/vegbien.sql: added more ~type tables for plots queries
schemas/vegbien.sql: added public_validations.rm_all_queries(schema), which removes all validations queries in a schema, or in all schemas (useful when recreating validations queries from the DDL export file)
schemas/util.sql: validation_views(): moved filtering out of _validations schemas to validatable_datasources() so that calling this function with no args gives the validation views in *all schemas as one would expect
schemas/util.sql: query_relations(): support passing in an input query instead of an output query, which will remove only in the datasource's schema. (note that it was not clear that passing in an input-side query was not previously supported.)
schemas/vegbien.sql: schema_anchor: clarified that this identifies this function's schema (for use in locating helper functions), but is not necessarily the schema operated on, as the comment implied
fix: schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: count top-level plots to match the input query
schemas/vegbien.sql: remake_diff_table(): instructions for how to regenerate the diff table: put these at the beginning of the table comment instead of the end, because they are the most important info about the table that a user needs to know
schemas/vegbien.sql: replaced custom %== operators with much simpler custom keys() functions. this avoids both the need to write out an = comparison for each field, and especially, also the need to write both an operator and a function implementing that operator.
schemas/util.sql, vegbien.sql: comment about "function option search_path": don't include `mk_set_search_path()` since that is no longer used
schemas/util.sql: use_own_schema(): renamed to use_schema() because this can be used for any type-linked schema
bugfix: rematerialize_out_view(): set_search_path() does not take the same arguments as mk_set_search_path() did, so need to use mk_search_path() instead
schemas/util.sql, vegbien.sql: use util.use_own_schema()/util.set_search_path() instead of EXECUTE util.mk_use_own_schema()/util.mk_set_search_path()
schemas/util.sql, vegbien.sql: do not declare IMMUTABLE SQL-language functions as STRICT, because this prevents them from being inlined (as documented in the util schema comment)
schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables
schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: added operator %== to match the rows by project_name
bugfix: schemas/util.sql: diff(text, text) and types that use its custom FULL JOIN matching: need to use separate operator %== for determining which records to put on the same row, so that the = filter for identical rows only excludes rows that are actually the same, not all rows with the same keys (which would usually remove all rows)
schemas/vegbien.sql: _plots_02_list_of_project_names: added = operator that causes FULL JOIN to only use some columns, so that rows for the same label column are put on the same row in the diff table
fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query
fix: schemas/vegbien.sql: rm_query_group(): schemas/vegbien.sql: util.drop_relations_like(): also need to drop ~type tables, so that they will be re-created for the new queries of that group
schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix
schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source
fix: schemas/vegbien.sql: public_validations._plots_* that use projectname: re-alias to project_name to match input queries. (reexporting apparently changes the relative order of the ~type tables.)
fix: schemas/vegbien.sql: _plots_03_count_of_all_plots_in_this_source: use top_plot instead of location because the corresponding input query includes just the top-level plots
schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully
schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)
fix: schemas/vegbien.sql: public_validations.rename_query_view(): fixed usage
schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources
schemas/vegbien.sql: added validatable_datasources()
schemas/vegbien.sql: validation_views(): allow leaving out schema to match all datasources
schemas/util.sql: show_views_like(): take a schema regexp like util.show_relations_like(), to allow matching views in all schemas
schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables
schemas/vegbien.sql: public_validations.remake_diff_table(in_view regclass): document how to regenerate the diff table, in a place that's readily available to someone using the table
fix: schemas/vegbien.sql: _traits_08_taxonname_trait_and_value_for_first_5000_records: renamed to _traits_08_taxonname_trait_and_value because this actually includes all the records, not just the first 5000. this uses the new public_validations.rename_query_view() to rename all associated tables and views, including handling truncated names.
bugfix: schemas/vegbien.sql: query_relations(), new_query_view_relation_name(): need to take into account the prefix when determining truncation, using the max_prefix_len param
schemas/vegbien.sql: added query_view_relation_max_prefix_len()
schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query
schemas/vegbien.sql: added new_query_view_relation_name()
schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query
fix: schemas/vegbien.sql: remake_diff_tables(): need to set function option search_path in order to limit the effects of `SET LOCAL search_path` (mk_set_search_path()) to the current function
fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views
schemas/vegbien.sql: added public_validations.query_views()
schemas/util.sql: show_views_like(): reversed argument order to match show_relations_like()
schemas/vegbien.sql: remake_diff_tables(): updated runtime now that row limit has been removed (same, 45 min)
