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/vegbien.sql: synced with DB, which reorders ~type tables
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)
fix: schemas/vegbien.sql: _traits_07_trait_value_and_units, _traits_08_taxonname_trait_and_value_for_first_5000_records: removed LIMIT to match the input queries
schemas/vegbien.sql: remake_diff_tables(): updated bien2_traits runtime (45 min) to include the now-properly-renamed views 6-8
schemas/vegbien.sql: remake_diff_tables(): documented runtime for bien2_traits (30 min)
fix: schemas/vegbien.sql: renamed output views to match input views
schemas/vegbien.sql: _traits_01_count_records (output side): documented runtime (4 min)
bugfix: schemas/vegbien.sql: source_by_shortname(): resolved ambiguous column references by renaming PL/pgSQL vars and adding table qualifiers
bugfix: schemas/vegbien.sql: source_by_shortname(): changed from STABLE to VOLATILE because the search_path can only be set in VOLATILE functions
bugfix: schemas/vegbien.sql: source_by_shortname(): can't be declared STRICT because it now has a 2nd param (schema_anchor) which is always NULL
bugfix: schemas/vegbien.sql: remake_diff_tables(): can't raise exception at end if had errors, because this would roll back the transaction. instead use new util.raise_error_notice().
schemas/vegbien.sql: added remake_diff_tables()
schemas/vegbien.sql: added validation_views()
schemas/vegbien.sql: remake_diff_table(): prepend ~ to the type table so that it sorts at the end, away from the main tables
bugfix: schemas/vegbien.sql: remake_diff_table(): can't have in_table/out_table inherit from each other, because inheritance also causes the rows of the parent table to be included in the child table. instead, they need to inherit from a common, empty table, as implemented by the new util.remake_diff_table().
schemas/vegbien.sql: added remake_diff_table()
bugfix: schemas/vegbien.sql: setting search_path locally: also 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 (http://www.postgresql.org/docs/9.1/static/sql-set.html#AEN75423)
bugfix: schemas/vegbien.sql: setting search_path locally: need to use `SET LOCAL search_path TO` instead of `PERFORM set_config(search_path, ..., is_local := true)` because the meaning of is_local refers to the entire transaction, not just the current function (as for LOCAL)
schemas/vegbien.sql: added rematerialize_in_view()
schemas/vegbien.sql: rematerialize_out_view(): pass a schema-qualified name to util.rematerialize_view() to avoid depending on the search_path to create it in the right schema
schemas/vegbien.sql: materialize_query(): support schema-qualified tables. this requires escaping tables in calls to this function.
schemas/vegbien.sql: added rematerialize_out_view(in_view regclass)
schemas/vegbien.sql: added rematerialize_out_view(datasource_schema text, out_view regclass)
bugfix: schemas/vegbien.sql: source_by_shortname(): added schema_anchor so that it always applies to the schema it's defined in, even if `public` is not in the search_path
schemas/vegbien.sql: functions with schema_anchors: documented that they must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime changes of search_path (schema elements are bound at inline time rather than runtime)
bugfix: schemas/vegbien.sql: functions with schema_anchors: must use plpgsql for these because the sql language does not support runtime changes of search_path (the schema elements they use are bound at inline time rather than runtime)
schemas/vegbien.sql: schema_null param: renamed to schema_anchor to clarify what this is for
fix: schemas/vegbien.sql: schema_null: clarified that this should always be left as the default value, not just usually
schemas/vegbien.sql: schema_null params: use schema_anchor instead of source for this, since it is guaranteed to exist regardless of which tables are in the VegBIEN schema
schemas/vegbien.sql: public: added schema_anchor (used with schema_null params)
schemas/vegbien.sql: removed no longer used concat() aggregate. use builtin string_agg() instead.
schemas/util.sql: schema_ident(): renamed to schema_esc() for clarity
schemas/vegbien.sql: added schema_anchor, for use with schema_null params
schemas/*.sql: make sure every COMMENT starts and ends on its own line, so that it appears correctly in the formats it's most likely to be read in (ie. in the DDL export, not the COMMENT edit box in pgAdmin)
validation/aggregating/*/*.sql, schemas/vegbien.sql, lib/runscripts/validations.pg.sql.run, inputs/bien2_traits/validations.sql: added _ to beginning of each view name so the validation views would sort at the top in the datasource's tables list. this will also make the validation result sets easily distinguishable from the data tables.
schemas/vegbien.sql: public_validations_*: merged into single public_validations schema, with type-specific prefixes for each query, so that datasources can use validations queries from multiple type categories, and so that each datasource doesn't have to indicate which validations output schema it's using
schemas/vegbien.sql: regenerated from DB, which adds an autogenerated column alias
