lib/runscripts/util.run: added $auto_fwd switch
bugfix: lib/runscripts/util.run: gateway(): need to use is_callable() rather than func_exists() to check whether the target exists, because external commands (eg. echo) are supported as targets, too
lib/sh/util.sh: added is_callable()
lib/runscripts/util.run: support custom handlers for all targets (gateway()) as well as targets w/o function (fallback())
lib/runscripts/table.run: remake_VegBIEN_mappings(): renamed to just mappings() since action make targets should be short names
lib/sh/util.sh: stderr_matches(): inline the stderr_matches alias to avoid needing to quote stderr_matches as "stderr_matches" in the most common use case (with pattern as a prefix env var)
bugfix: lib/sh/util.sh: stderr_matches(): when passing `pattern=...` as a prefix env var, must be invoked as `"stderr_matches"` to avoid the env var applying to the prep_try portion of the stderr_matches alias
added schemas/VegCore/Brad_Boyle/bien3_data_provenance_use_cases.docx* from e-mail from Brad
schemas/vegbien.sql: _plots_08_list_of_plots_which_use_percent_cover, _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: reran with fixes, which removes the incorrectly auto-added copies columns. (they were only able to be auto-added because the tables had no rows.)
bugfix: drop_column(regclass[]): need to run `SELECT NULL::void;` at end of function to avoid folding away functions called in previous query
fix: schemas/util.sql: diff(regclass, regclass): moved try_create() of copies column in parent table to auto_rm_freq() so that it would only happen if both tables actually contain a copies column (otherwise, the try_create() will create an empty copies column if both tables are empty)
schemas/util.sql: try_create(): also handle "child table is missing column" errors
schemas/util.sql: added coalesce(anyarray), which can be used to force evaluation of all values of a COALESCE
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
inputs/SALVIAS/validations.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
validation/aggregating/plots/FIA/bien3_validations_fia_input.sql: _plots_19_count_of_inventories_per_plot_in_each_project: renamed to _plots_19_count_of_censuses_per_plot_in_each_project for clarity
validation/aggregating/plots/FIA/bien3_validations_fia_input.sql*: updated from Brad's latest e-mail
schemas/util.sql: EXCEPTION blocks with multiple exception types: use OR to merge exception types into one WHEN block
schemas/vegbien.sql: public_validations: schema comment: changed "to sync the queries with schemas/vegbien.sql" to "to reset the queries to what's in schemas/vegbien.sql" for clarity
fix: schemas/vegbien.sql: schema comment: to reset the key and value columns for all validations queries: updated running of custom keys() functions to use keys() types instead
schemas/vegbien.sql: schema comment: to sync the queries with schemas/vegbien.sql: use new public_validations.rm_output_queries() instead of rm_all_queries() to leave the input queries in place
schemas/vegbien.sql: schema comment: documented how to reset the key and value columns for all validations queries
schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): indicate in the type comment that the keys() type is autogenerated, so it can be distinguished from custom keys() types when bulk-regenerating keys() types
bugfix: schemas/util.sql: show_relations_like(): also need to include composite types, as these are also relations (and are expected to be included by callers of show_relations_like())
bugfix: schemas/vegbien.sql: rm_output_queries(): also need to include keys_* and values__* types, as these are also associated with the query
schemas/util.sql: added debug_print_func_call(text) and use it where applicable
schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match
schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row
bugfix: schemas/vegbien.sql: public_validations.validation_views(): need to include views with letters after the query # (eg. _plots_06a_list_of_stems)
schemas/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.
bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if all tables have all 1s
schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if all tables have all 1s
schemas/util.sql: added freq_always_1(regclass[])
schemas/util.sql: added drop_column(regclass[])
schemas/util.sql: added parent(regclass)
schemas/util.sql: try_create(): also handle not_null_violation, which is thrown when trying to add a NOT NULL column to a parent table, which cascades to a child table whose values for the new column will be NULL
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
bugfix: inputs/SALVIAS/validations.sql: plots_07_list_of_plots_with_counts_of_individuals_per_species: renamed to _plots_07_list_of_plots*which_use*_... because this query is not intended to include the actual counts, just to say which plots have them (the correct "which use" wording is also used in queries #8, 9)
web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented how to prevent incorrect query plans (`SET enable_seqscan = off;`, etc.)
web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented that incorrect query plans are an ongoing bug in Postgres, because it does not support index hints and by default does not follow the join order. specifically, Postgres often does the following things in query plans which should normally never be done:...
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.)
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: added `SET enable_seqscan = off;` to match what is done by rematerialize_out_view() to run the queries properly
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
web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added description of join_collapse_limit config param (which should be turned off, although it is on by default). added links for using TIDs ("the fastest possible access to a single row").
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)
web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added links for troubleshooting autovacuuming (which can slow queries down significantly when it isn't happening for any tables)
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.
planning/meetings/BIEN conference call availability.xlsx: updated
bugfix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: need to escape the quotes in \set ... 'SALVIAS'
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `SET search_path TO public;` since this is the default
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: search_path: removed public_validations since we are not creating views
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: use psql var :datasource instead of current_schema() so that the queries are runnable without special configuration of the search_path
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `CREATE OR REPLACE VIEW` so the validations views are not unintentionally replaced when running this file
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB
schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table
fix: inputs/SALVIAS/validations.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: changed columns to match output query
schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table
fix: inputs/SALVIAS/validations.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: changed types to match output query
bugfix: inputs/SALVIAS/validations.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: changed summarizing column from mean_cover->totalpercentcover to match output query
bugfix: inputs/SALVIAS/validations.sql: _plots_10a_aggregate_observation_individual_counts: changed individual_id type to match output query
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
