bugfix: schemas/util.sql: diff(text, text): also need to cast left_/right_ to base type for the IS DISTINCT FROM filter, because the WHERE clause apparently does not use columns from the SELECT list, even though GROUP BY and ORDER BY do

schemas/util.sql: added to_freq(regclass, drop_if_always_1)

schemas/util.sql: added auto_rm_freq(regclass)

schemas/util.sql: added freq_always_1(regclass)

bugfix: schemas/util.sql: diff(regclass, regclass): need to create a diff when the # of copies of a row differs between the tables. this uses new util.to_freq().

schemas/util.sql: added to_freq(regclass)

schemas/util.sql: added populate_table(regclass, text)

schemas/util.sql: added copy_types_and_data(regclass, text)

schemas/vegbien.sql: public_validations schema comment: added instructions to change the key and value columns for a validations query

schemas/vegbien.sql: implemented _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj

schemas/vegbien.sql: implemented _plots_09_list_of_plots_which_use_line_intercept

schemas/vegbien.sql: public_validations: queries that use EXISTS: join locationevent.plot_id to plot.plot_id directly instead of going via location.plot_location_id

schemas/vegbien.sql: implemented _plots_08_list_of_plots_which_use_percent_cover

schemas/vegbien.sql: implemented _plots_07_list_of_plots_which_use_counts_of_indiv_per_species

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/postgresql.conf: log_min_messages: dropped the verbosity back down to the default, to avoid clogging up the logs

schemas/vegbien.sql: locationevent: documented `VACUUM ANALYZE` runtime (20 min)

schemas/postgresql.conf: log_min_messages: show what autovacuum is doing

fix: schemas/postgresql.conf: disable autovacuum_vacuum_cost_delay to avoid stalling autovacuuming due to a concurrent query, as this can prevent autovacuuming from happening altogether (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Resource%20Consumption:%2018.4.4.%20Cost-based%20Vacuum%20Delay)

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/Makefile: `%/install: vegbien.sql`: also need to match `public_validations` when used as a schema-qualifier (public_validations._), and after a cast (::) to a schema-qualified type. these occur in schema-qualified casts to the custom return type in the keys() functions.

bugfix: schemas/Makefile: `%/install: vegbien.sql`: sed expr: need to use '' instead of "" because $(*q) may contain "

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/util.sql: create_if_not_exists(): also support `CREATE FUNCTION` (by handling duplicate_function exceptions)

schemas/util.sql: mk_keys_func(): create the keys() function directly from the keys record type, to support creating just a custom keys record type rather than also a custom keys() function (which is fairly complicated to do, thanks to the need to define a separate custom return type)

bugfix: schemas/util.sql: relation_type(relation_type_char "char"): need to handle TYPEs as well, because these are also listed in pg_class (and should be deleted by drop_relations_like())

schemas/util.sql: added show_types_like()

bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

schemas/vegbien.sql: locationevent: documented `UPDATE locationevent SET place_visit_id = place_visit_id` runtime (>~1 h)

schemas/util.sql: added relation_type(regtype) so drop_relation() would work on TYPEs, too. TYPEs are sometimes used as a function return type linked to a particular table (eg. in the keys() functions), and should be dropped along with the table by util.drop_relations_like().

schemas/util.sql: drop_relation(regclass): support any type that has a util.relation_type()

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

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use `CREATE FUNCTION` instead of `CREATE OR REPLACE FUNCTION` so that the function is only created if it does not exist, to avoid overwriting a custom keys() function

schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries

bugfix: schemas/util.sql: uses of util.col_cast.col_name: need to wrap in quote_ident()

schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use named return type to avoid complicated edge cases with 1-column records, which prevent (values_(...)).* from working correctly

schemas/util.sql: added mk_typed_cols_list(col_cast[])

schemas/util.sql: added prefixed_name()

schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.qual_name() to create the function name, for clarity

schemas/vegbien.sql: public_validations: auto-added values_() functions for all queries

schemas/util.sql: util.remake_diff_table(): viewing the table in human-readable form: use just the output side's values_() columns, to avoid repeating columns on both sides that are the same and put just the changed columns side-by-side

bugfix: schemas/util.sql: mk_keys_func(regtype): values() function: must be called values_() instead because `values` is a keyword

schemas/util.sql: mk_keys_func(regtype): also add values() function for use in displaying the diff table

schemas/util.sql: mk_keys_func(regtype, col_cast[]): allow changing the name of the generated function

schemas/util.sql: `RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM`: use simpler `RAISE` to rethrow error

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.create_if_not_exists() to avoid trying to overwrite a custom keys() function the user has written

schemas/vegbien.sql: public_validations: auto-added keys() functions for all queries

schemas/util.sql: diff(text, text): automatically create a keys() function for the base type. this avoids the need to create keys() functions manually for the numerous queries that need them.

schemas/util.sql: added mk_keys_func(regtype)

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): need to handle 1-column records specially, because Postgres does not allow returning a 1-column record when there are OUT params

schemas/util.sql: added typed_cols(regtype)

schemas/util.sql: added mk_keys_func(regtype, col_cast[])

bugfix: schemas/util.sql: raise(): need to use $__BODY1$ in case msg contains $BODY1$ (in SQL)

bugfix: schemas/util.sql: mk_out_params(): need COALESCE around string_agg()

bugfix: schemas/util.sql: eval(): don't declare it STRICT because it should throw an error if you try to execute NULL

schemas/vegbien.sql: synced with DB, which reorders ~type tables

schemas/util.sql: added mk_out_params()

schemas/util.sql: mk_diff_query(): indented left_ table to line up vertically with right_, for easier comparison of the left_/right_ table names

schemas/util.sql: mk_diff_query(): removed special handling for CROSS JOIN because this is now handled by diff(text, text) using `FULL JOIN ON true`. this simplification allows mk_diff_query() to contain just the template structure of the FULL JOIN, without _if() calls that decrease readability.

schemas/util.sql: diff(text, text): handle the CROSS JOIN special case using `FULL JOIN ON true`, to allow mk_diff_query() to support just a straighforward FULL JOIN. this also ensures that all diff queries use the same FULL JOIN template.

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

bugfix: schemas/Makefile: `%/install: vegbien.sql`: don't enclose public_validations in "" because this causes a comment that says to search for `SET search_path = public_validations, pg_catalog;` to become mangled

schemas/vegbien.sql: synced with DB, which reorders ~type tables

fix: schemas/Makefile: moved comment about publishing a schema to %/publish

schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match

schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match

schemas/util.sql: removed no longer used raise_notice(). use util.raise(NOTICE, ...) instead.

schemas/util.sql: use util.raise(NOTICE, ...) instead of util.raise_notice()

schemas/util.sql: raise(type text, msg text): documented the possible options for the type param

fix: schemas/util.sql: raise(): util.eval(): turn off verbose_ mode to avoid printing debug statements about debug statements

schemas/util.sql: eval(): added verbose_ option like for eval2set()

schemas/util.sql: added raise(type text, msg text)

fix: schemas/vegbien.sql: plots* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)