Activity
From 02/13/2014 to 03/14/2014
03/14/2014
- 09:09 PM Revision 12718: bugfix: /README.TXT: Maintenance: VegCore data dictionary: apply new data dict mappings: need to use import rather than mappings runscript target, to rename the staging tables
- 09:06 PM Revision 12717: bugfix: /README.TXT: Maintenance: VegCore data dictionary: also need to apply new data dict mappings on vegbiendev
- 08:19 PM Revision 12716: fix: /README.TXT: Maintenance: VegCore data dictionary: added steps to apply the new data dictionary mappings to the datasource mappings and staging tables
- 07:53 PM Revision 12715: bugfix: lib/runscripts/util.run: $auto_ignore: need to unexport it so don't pass this to invoked scripts except through fwd()
- 07:35 PM Revision 12714: added inputs/run, which runs all the inputs' runscripts using the new auto-forwarding
- 07:34 PM Revision 12713: bugfix: lib/runscripts/util.run: auto_fwd's fallback() must be set *after* auto_ignore's fallback() to overwrite it (auto_ignore should only apply if an error would otherwise have been generated by the fallback)
- 07:30 PM Revision 12712: lib/runscripts/util.run: fwd(): support subdirs that don't contain a runscript, so that the default value of @subdirs will work in most cases
- 07:29 PM Revision 12711: lib/runscripts/util.run: fwd(): set default @subdirs (`{.,}*/`)
- 07:26 PM Revision 12710: lib/sh/util.sh: added enter_top_dir and use it in in_top_dir
- 07:12 PM Revision 12709: fix: lib/sh/util.sh: commands run inside $(...): need to run with log++ so that these aren't normally debug-printed
- 06:41 PM Revision 12708: lib/sh/util.sh: added pv(), which debug-prints var(s)
- 06:40 PM Revision 12707: lib/sh/util.sh: added wildcard.()
- 06:40 PM Revision 12706: lib/sh/util.sh: added wildcard/()
- 06:40 PM Revision 12705: lib/sh/util.sh: added esc_args()
- 06:33 PM Revision 12704: web/links/index.htm: updated to Firefox bookmarks: Google Drive: listed bugs that make it very difficult to use (the need to re-download all files when reconnecting a client to an account). added recommendation not to use it (unstable).
- 05:25 PM Revision 12703: removed unused inputs/table.run. inputs/*/table.run include lib/runscripts/table.run directly.
- 05:02 PM Revision 12702: lib/runscripts/datasrc_dir.run: removed postprocess(), which now does the same thing its auto-forwarded equivalent would
- 05:01 PM Revision 12701: lib/runscripts/datasrc_dir.run: removed separate @table_subdirs, because the table-only targets can now safely be invoked on all subdirs, being auto-ignored in subdirs that don't support them
- 04:53 PM Revision 12700: lib/runscripts/util.run: fwd(): enable $auto_ignore so that each subdir doesn't have to have a definition for the forwarded target
- 04:52 PM Revision 12699: lib/runscripts/util.run: added $auto_ignore switch, which causes fallback() not to generate an error that a non-existant target doesn't exist
- 03:55 PM Revision 12698: lib/runscripts/datasrc_dir.run: use new fwd_self alias
- 03:55 PM Revision 12697: lib/runscripts/util.run: added fwd_self alias
- 03:49 PM Revision 12696: lib/runscripts/datasrc_dir.run: enable $auto_fwd, to create the functionality of lib/forwarding.Makefile's `%` target
- 03:47 PM Revision 12695: lib/runscripts/util.run: added $auto_fwd switch
- 03:36 PM Revision 12694: 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
- 03:32 PM Revision 12693: lib/sh/util.sh: added is_callable()
- 03:23 PM Revision 12692: lib/runscripts/util.run: support custom handlers for *all* targets (gateway()) as well as targets w/o function (fallback())
- 03:03 PM Revision 12691: lib/runscripts/table.run: remake_VegBIEN_mappings(): renamed to just mappings() since action make targets should be short names
- 07:32 AM Revision 12690: 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)
- 07:29 AM Revision 12689: 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
- 06:38 AM Revision 12688: added schemas/VegCore/Brad_Boyle/bien3_data_provenance_use_cases.docx* from e-mail from Brad
03/13/2014
- 06:53 PM Revision 12687: 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.)
- 06:42 PM Revision 12686: bugfix: drop_column(regclass[]): need to run `SELECT NULL::void;` at end of function to avoid folding away functions called in previous query
- 06:40 PM Revision 12685: 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)
- 06:33 PM Revision 12684: schemas/util.sql: try_create(): also handle "child table is missing column" errors
- 05:33 PM Revision 12683: schemas/util.sql: added coalesce(anyarray), which can be used to force evaluation of all values of a COALESCE()
- 05:14 PM Revision 12682: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 05:13 PM Revision 12681: fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
- 05:04 PM Revision 12680: schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
- 05:03 PM Revision 12679: inputs/SALVIAS/validations.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
- 09:08 AM Revision 12678: 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
- 09:00 AM Revision 12677: validation/aggregating/plots/FIA/bien3_validations_fia_input.sql*: updated from Brad's latest e-mail
- 02:06 AM Revision 12676: schemas/util.sql: EXCEPTION blocks with multiple exception types: use OR to merge exception types into one WHEN block
- 01:50 AM Revision 12675: 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
- 01:46 AM Revision 12674: 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
- 01:14 AM Revision 12673: 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
- 01:12 AM Revision 12672: schemas/vegbien.sql: schema comment: documented how to reset the key and value columns for all validations queries
03/12/2014
- 11:56 PM Revision 12671: 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
- 11:53 PM Revision 12670: 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())
- 11:49 PM Revision 12669: bugfix: schemas/vegbien.sql: rm_output_queries(): also need to include keys_* and values__* types, as these are also associated with the query
- 11:40 PM Revision 12668: schemas/util.sql: added debug_print_func_call(text) and use it where applicable
- 11:33 PM Revision 12667: schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match
- 06:26 PM Revision 12666: schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row
- 06:18 PM Revision 12665: bugfix: schemas/vegbien.sql: public_validations.validation_views(): need to include views with letters after the query # (eg. _plots_06a_list_of_stems)
- 05:41 PM Revision 12664: schemas/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.
- 05:40 PM Revision 12663: bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if *all* tables have all 1s
- 05:38 PM Revision 12662: schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if *all* tables have all 1s
- 05:36 PM Revision 12661: schemas/util.sql: added freq_always_1(regclass[])
- 05:35 PM Revision 12660: schemas/util.sql: added drop_column(regclass[])
- 05:04 PM Revision 12659: schemas/util.sql: added parent(regclass)
- 04:48 PM Revision 12658: 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
- 04:44 PM Revision 12657: 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
- 04:13 PM Revision 12656: schemas/util.sql: added to_freq(regclass, drop_if_always_1)
- 04:04 PM Revision 12655: schemas/util.sql: added auto_rm_freq(regclass)
- 03:53 PM Revision 12654: schemas/util.sql: added freq_always_1(regclass)
- 03:00 PM Revision 12653: 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().
- 02:44 PM Revision 12652: schemas/util.sql: added to_freq(regclass)
- 02:43 PM Revision 12651: schemas/util.sql: added populate_table(regclass, text)
- 01:11 PM Revision 12650: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 12:53 PM Revision 12649: schemas/util.sql: added copy_types_and_data(regclass, text)
- 04:44 AM Revision 12648: schemas/vegbien.sql: public_validations schema comment: added instructions to change the key and value columns for a validations query
- 04:41 AM Revision 12647: schemas/vegbien.sql: implemented _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj
- 03:44 AM Revision 12646: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 03:44 AM Revision 12645: fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
- 03:35 AM Revision 12644: schemas/vegbien.sql: implemented _plots_09_list_of_plots_which_use_line_intercept
- 03:20 AM Revision 12643: 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
- 03:04 AM Revision 12642: schemas/vegbien.sql: implemented _plots_08_list_of_plots_which_use_percent_cover
- 12:04 AM Revision 12641: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 12:01 AM Revision 12640: schemas/vegbien.sql: implemented _plots_07_list_of_plots_which_use_counts_of_indiv_per_species
03/11/2014
- 09:57 PM Revision 12639: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 09:56 PM Revision 12638: 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)
- 04:05 PM Revision 12637: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented how to prevent incorrect query plans (`SET enable_seqscan = off;`, etc.)
- 03:38 PM Revision 12636: 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:
- * performs a sequential scan when an index is available (because it incorrectly thinks there are too many dead rows i...
03/07/2014
- 10:49 PM Revision 12635: 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
- 09:53 PM Revision 12634: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 09:50 PM Revision 12633: schemas/vegbien.sql: plot: removed explicit column lists added in the autorename of plot.location_id->plot_id
- 09:41 PM Revision 12632: schemas/vegbien.sql: plot: renamed pkey to plot_id. note that the field is autorenamed in all validation views which use it.
- 09:18 PM Revision 12631: schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location
- 08:55 PM Revision 12630: bugfix: schemas/vegbien.sql: locationevent: added missing fkey on place_visit_id
- 04:42 PM Revision 12629: 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.)
- 05:51 AM Revision 12628: 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
- 05:42 AM Revision 12627: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 05:35 AM Revision 12626: 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.
- 05:25 AM Revision 12625: 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).
- 05:23 AM Revision 12624: schemas/vegbien.sql: locationevent: documented `CREATE INDEX locationevent_place_visit_id` runtime (3 min)
- 04:53 AM Revision 12623: fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries
- 02:26 AM Revision 12622: 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").
03/06/2014
- 10:45 PM Revision 12621: 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).
- 10:26 PM Revision 12620: schemas/postgresql.conf: log_min_messages: dropped the verbosity back down to the default, to avoid clogging up the logs
- 10:21 PM Revision 12619: schemas/vegbien.sql: locationevent: documented `VACUUM ANALYZE` runtime (20 min)
- 09:51 PM Revision 12618: schemas/postgresql.conf: log_min_messages: show what autovacuum is doing
- 09:40 PM Revision 12617: 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)
- 09:37 PM Revision 12616: 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)
- 07:35 PM Revision 12615: schemas/vegbien.sql: location: documented `CREATE INDEX plot_source_id` runtime (5 min)
- 07:30 PM Revision 12614: 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.
- 11:30 AM Revision 12613: planning/meetings/BIEN conference call availability.xlsx: updated
- 11:06 AM Revision 12612: bugfix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: need to escape the quotes in \set ... 'SALVIAS'
- 11:04 AM Revision 12611: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `SET search_path TO public;` since this is the default
- 11:03 AM Revision 12610: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: search_path: removed public_validations since we are not creating views
- 11:02 AM Revision 12609: 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
- 10:59 AM Revision 12608: 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
- 10:57 AM Revision 12607: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB
- 08:57 AM Revision 12606: schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table
- 08:52 AM Revision 12605: fix: inputs/SALVIAS/validations.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: changed columns to match output query
- 08:31 AM Revision 12604: schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table
- 08:29 AM Revision 12603: 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
- 08:14 AM Revision 12602: 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
- 08:12 AM Revision 12601: bugfix: inputs/SALVIAS/validations.sql: _plots_10a_aggregate_observation_individual_counts: changed individual_id type to match output query
- 02:18 AM Revision 12600: 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.
- 01:59 AM Revision 12599: bugfix: schemas/Makefile: `%/install: vegbien.sql`: sed expr: need to use '' instead of "" because $(*q) may contain "
- 12:33 AM Revision 12598: bugfix: schemas/vegbien.sql: _plots_10a_aggregate_observation_individual_counts: need to use taxonoccurrence.sourceaccessioncode, not aggregateoccurrence.sourceaccessioncode, because aggregateoccurrence.sourceaccessioncode is not populated
- 12:09 AM Revision 12597: 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())
- 12:07 AM Revision 12596: 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
- 12:00 AM Revision 12595: schemas/util.sql: create_if_not_exists(): also support `CREATE FUNCTION` (by handling duplicate_function exceptions)
03/05/2014
- 11:53 PM Revision 12594: 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)
- 01:01 PM Revision 12593: 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())
- 12:50 PM Revision 12592: schemas/util.sql: added show_types_like()
- 12:42 PM Revision 12591: 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**)
- 12:25 PM Revision 12590: 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**)
- 12:13 PM Revision 12589: schemas/vegbien.sql: locationevent: documented `UPDATE locationevent SET place_visit_id = place_visit_id` runtime (>~1 h)
- 07:28 AM Revision 12588: 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().
- 07:24 AM Revision 12587: schemas/util.sql: drop_relation(regclass): support any type that has a util.relation_type()
- 07:23 AM Revision 12586: 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
- 01:02 AM Revision 12585: schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.
- 12:59 AM Revision 12584: 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.
- 12:32 AM Revision 12583: fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger
03/04/2014
- 05:56 PM Revision 12582: bugfix: schemas/vegbien.sql: public_validations.query_view_relation_max_prefix_len(): added values__ prefix
- 05:52 PM Revision 12581: 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
- 05:48 PM Revision 12580: schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries
- 05:47 PM Revision 12579: bugfix: schemas/util.sql: uses of util.col_cast.col_name: need to wrap in quote_ident()
- 05:40 PM Revision 12578: schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries
- 05:39 PM Revision 12577: 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
- 05:22 PM Revision 12576: schemas/util.sql: added mk_typed_cols_list(col_cast[])
- 05:20 PM Revision 12575: schemas/util.sql: added prefixed_name()
- 05:18 PM Revision 12574: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.qual_name() to create the function name, for clarity
- 04:56 PM Revision 12573: schemas/vegbien.sql: public_validations: auto-added values_() functions for all queries
- 04:53 PM Revision 12572: 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
- 04:45 PM Revision 12571: bugfix: schemas/util.sql: mk_keys_func(regtype): values() function: must be called values_() instead because `values` is a keyword
- 04:31 PM Revision 12570: schemas/util.sql: mk_keys_func(regtype): also add values() function for use in displaying the diff table
- 04:19 PM Revision 12569: schemas/util.sql: mk_keys_func(regtype, col_cast[]): allow changing the name of the generated function
- 03:55 PM Revision 12568: schemas/util.sql: `RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM`: use simpler `RAISE` to rethrow error
- 03:53 PM Revision 12567: 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
- 03:49 PM Revision 12566: schemas/vegbien.sql: public_validations: auto-added keys() functions for all queries
- 03:41 PM Revision 12565: 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.
- 03:35 PM Revision 12564: schemas/util.sql: added mk_keys_func(regtype)
- 03:34 PM Revision 12563: 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
- 03:02 PM Revision 12562: schemas/util.sql: added typed_cols(regtype)
- 02:29 PM Revision 12561: schemas/util.sql: added mk_keys_func(regtype, col_cast[])
- 02:27 PM Revision 12560: bugfix: schemas/util.sql: raise(): need to use $__BODY1$ in case msg contains $BODY1$ (in SQL)
- 02:23 PM Revision 12559: bugfix: schemas/util.sql: mk_out_params(): need COALESCE(..., ) around string_agg()
- 02:22 PM Revision 12558: bugfix: schemas/util.sql: eval(): don't declare it STRICT because it should throw an error if you try to execute NULL
- 02:20 PM Revision 12557: schemas/vegbien.sql: synced with DB, which reorders ~type tables
- 01:59 PM Revision 12556: schemas/util.sql: added mk_out_params()
- 01:42 AM Revision 12555: schemas/util.sql: mk_diff_query(): indented left_ table to line up vertically with right_, for easier comparison of the left_/right_ table names
- 01:40 AM Revision 12554: 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.
- 01:34 AM Revision 12553: 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.
02/28/2014
- 11:17 PM Revision 12552: bugfix: schemas/vegbien.sql: public_validations: schema comment: also need to re-create keys() functions after running public_validations.rm_all_queries()
- 11:13 PM Revision 12551: 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.
- 11:11 PM Revision 12550: schemas/vegbien.sql: added new ~type tables
- 10:45 PM Revision 12549: fix: lib/phpPgAdmin.login.php.diff: "For user public_, leave password blank" instruction: moved this to right under the Password field and increased the font size so people would be more likely to see it
- 10:28 PM Revision 12548: /Makefile: added separate phppgadmin-Linux target to avoid needing to run the entire postgres-Linux target whenever http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down (after some system updates)
- 10:03 PM Revision 12547: 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().
- 09:49 PM Revision 12546: fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries
- 08:15 PM Revision 12545: 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())
- 07:57 PM Revision 12544: 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
- 07:56 PM Revision 12543: 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
- 07:51 PM Revision 12542: schemas/vegbien.sql: synced with DB, which reorders ~type tables
- 07:34 PM Revision 12541: fix: schemas/Makefile: moved comment about publishing a schema to %/publish
- 05:27 PM Revision 12540: lib/Firefox_bookmarks.reformat.csv: "page's description": changed to "page's own description" to clarify that this is a description provided by the page itself
- 05:25 PM Revision 12539: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added links for error reporting levels and how to hide stack traces in psql. SQL: added links for recursive queries, which can be used to traverse hierarchical (parent-pointer) tables.
02/27/2014
- 07:56 PM Revision 12538: fix: inputs/SALVIAS/validations.sql: renamed SiteCode to plot_code to match output queries
- 07:53 PM Revision 12537: schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match
- 07:49 PM Revision 12536: schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match
- 07:45 PM Revision 12535: schemas/util.sql: removed no longer used raise_notice(). use util.raise(NOTICE, ...) instead.
- 07:41 PM Revision 12534: schemas/util.sql: use util.raise(NOTICE, ...) instead of util.raise_notice()
- 07:37 PM Revision 12533: schemas/util.sql: raise(type text, msg text): documented the possible options for the type param
- 07:34 PM Revision 12532: fix: schemas/util.sql: raise(): util.eval(): turn off verbose_ mode to avoid printing debug statements about debug statements
- 07:32 PM Revision 12531: schemas/util.sql: eval(): added verbose_ option like for eval2set()
- 07:28 PM Revision 12530: schemas/util.sql: added raise(type text, msg text)
- 07:09 PM Revision 12529: fix: schemas/vegbien.sql: _plots_* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)
- 07:08 PM Revision 12528: fix: schemas/vegbien.sql: added back _traits_* ~type tables
- 07:02 PM Revision 12527: fix: schemas/vegbien.sql: _plots_* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)
- 06:58 PM Revision 12526: inputs/SALVIAS/validations.sql: use plot_code instead of plotcode for easier readability
- 06:42 PM Revision 12525: 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
- 06:36 PM Revision 12524: 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
- 04:37 PM Revision 12523: schemas/vegbien.sql: top_plot view: renamed to plot, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)
- 04:12 PM Revision 12522: 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)
- 03:51 PM Revision 12521: 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)
- 02:20 PM Revision 12520: schemas/vegbien.sql: source_by_shortname(): documented exact time that the slow query ran for (5013s = ~1.5 hours)
- 02:07 PM Revision 12519: schemas/vegbien.sql: public_validations: schema comment: documented how to sync the queries with schemas/vegbien.sql
- 02:05 PM Revision 12518: 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)
- 01:49 PM Revision 12517: 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
- 01:27 PM Revision 12516: 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 .
- 12:43 PM Revision 12515: 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)
- 07:59 AM Revision 12514: schemas/util.sql: eval2val(): made it STABLE so that functions that use it can themselves be inlined. (Postgres apparently ignores the STABLE qualifier, without warning, if the function contains any VOLATILE statements.)
- 06:51 AM Revision 12513: 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`.)
- 01:11 AM Revision 12512: fix: schemas/vegbien.sql: location: added index on top_plot
- 01:11 AM Revision 12511: fix: schemas/vegbien.sql: location: added index on top_plot
- 01:06 AM Revision 12510: fix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: changed columns to match input query
- 01:04 AM Revision 12509: schemas/vegbien.sql: added more ~type tables for plots queries
02/26/2014
- 11:58 PM Revision 12508: fix: inputs/SALVIAS/validations.sql: plotMetadata.SiteCode: need to match types with the output query column
- 11:44 PM Revision 12507: 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)
- 11:29 PM Revision 12506: 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
- 10:58 PM Revision 12505: 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.)
- 10:50 PM Revision 12504: schemas/util.sql: added schema_regexp(regclass)
- 10:30 PM Revision 12503: 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
- 10:05 PM Revision 12502: schemas/util.sql: drop_relations_like(): use util.schema_regexp()
- 10:02 PM Revision 12501: schemas/util.sql: added schema_regexp(schema_anchor)
- 01:16 PM Revision 12500: fix: schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: count *top-level* plots to match the input query
- 12:29 PM Revision 12499: 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
- 11:58 AM Revision 12498: schemas/util.sql: diff(text, text): documented how to write a custom keys() function to match up rows using a subset of the columns (including a sample keys() function template)
- 11:37 AM Revision 12497: 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.
- 10:42 AM Revision 12496: bugfix: schemas/util.sql: diff(text, text): in the %== comparison, it turns out you *do* need to cast the values to the *same* base type, *even though* this is optional when using a custom %==
02/25/2014
- 11:51 PM Revision 12495: schemas/util.sql: remake_diff_table(): prepend the "view this table in human-readable form" comment instead of appending because it is more important than the "contents generated from" comment previously added
- 11:49 PM Revision 12494: schemas/util.sql: added prepend_comment()
- 11:39 PM Revision 12493: schemas/util.sql: removed no longer used mk_set_search_path(VARIADIC schemas text[]). use mk_search_path() instead.
- 11:36 PM Revision 12492: schemas/util.sql, vegbien.sql: comment about "function option search_path": don't include `mk_set_search_path()` since that is no longer used
- 11:35 PM Revision 12491: schemas/util.sql: diff(text, text): always use the schema of col_type_null (the common base type) as the search_path, since any custom %== operator for it will always be in the same schema as it
- 11:28 PM Revision 12490: schemas/util.sql: use_own_schema(): renamed to use_schema() because this can be used for any type-linked schema
- 11:23 PM Revision 12489: 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
- 11:19 PM Revision 12488: schemas/util.sql: use_own_schema(): auto-append util to the search_path to enable use of util operators
- 11:11 PM Revision 12487: schemas/util.sql: mk_set_search_path(): no need to debug_print_return_value() anymore because functions now use set_search_path() (or something that calls it), which debug-prints the statement (`EXECUTE util.mk_set_search_path()` did not)
- 11:06 PM Revision 12486: schemas/util.sql: mk_search_path(), mk_set_search_path(text...): auto-append util to the search_path to enable use of util operators
- 10:49 PM Revision 12485: schemas/util.sql: removed no longer used mk_use_own_schema(). use util.use_own_schema() instead.
- 10:48 PM Revision 12484: 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()
- 10:42 PM Revision 12483: schemas/util.sql: added use_own_schema()
- 10:40 PM Revision 12482: schemas/util.sql: added set_search_path()
- 10:36 PM Revision 12481: schemas/util.sql: runnable_sql(): don't output search_path before a SET statement
- 10:31 PM Revision 12480: schemas/util.sql: added is_set_stmt()
- 09:28 PM Revision 12479: schemas/util.sql: diff(text, text): use mk_diff_query()'s new cols param to avoid a nested SELECT
- 09:17 PM Revision 12478: schemas/util.sql: mk_diff_query(): parameterized the customizable parts of the query, to allow them to be replaced with their EXPLAIN expansion (which is what we ultimately want, so that the query does not refer to any internal tables or views)
- 09:02 PM Revision 12477: schemas/util.sql: diff(text, text): use util.mk_diff_query(). this splits the complex query-generation code apart from the query-execution code.
- 08:51 PM Revision 12476: bugfix: schemas/util.sql: undid r12472 because the command needed to limit the effects of any `SET LOCAL search_path` to the current function will unfortunately also clear any existing search_path, which may be needed for the eval query to execute
- 08:25 PM Revision 12475: schemas/util.sql: added mk_diff_query()
- 08:13 PM Revision 12474: schemas/util.sql: debug_print_sql(): use runnable_sql()
- 08:12 PM Revision 12473: schemas/util.sql: added runnable_sql()
- 08:07 PM Revision 12472: fix: schemas/util.sql: eval2*(): added search_path function option in order to limit the effects of any `SET LOCAL search_path` in the invoked query to the current function. however, plain eval() is not changed because it is often used to execute a `SET LOCAL search_path` in the calling function.
- 06:03 PM Revision 12471: schemas/util.sql: diff(text, text): removed unnecessary casts to the base type in the join condition and the WHERE filter. these had been presumed necessary due to errors, but the errors turned out to be caused by the operator not being in the search_path. note that the casts in the columns list are still needed, as described in the associated comment.
- 05:40 PM Revision 12470: schemas/util.sql: materialize_query(): add a comment on the table with the query it was generated from
- 05:39 PM Revision 12469: schemas/util.sql: debug_print_sql(): util.mk_set_search_path(): use for_printing := true to comment out LOCAL
- 04:11 PM Revision 12468: schemas/util.sql: mk_set_search_path(text): include LOCAL commented out, because the user might want to run it with another statement *as a single command*, in which case it would be useful because they will be in the same transaction (http://www.postgresql.org/docs/9.3/static/sql-set.html#AEN81154)
- 04:09 PM Revision 12467: schemas/util.sql: mk_set_search_path(): added for_printing option like for mk_set_search_path(text)
- 04:04 PM Revision 12466: schemas/util.sql: mk_set_search_path(): support creating a SET search_path statement for display as well, which would exclude LOCAL because it doesn't work as a standalone command (http://www.postgresql.org/docs/9.3/static/sql-set.html#AEN81154)
- 02:48 PM Revision 12465: schemas/util.sql: debug_print_sql(): include the search_path in case the query contains search_path-dependent elements (such as operators)
- 02:45 PM Revision 12464: schemas/util.sql: util.explain2notice_msg(): add newline before and after to visually separate it from other debug info
- 02:30 PM Revision 12463: schemas/util.sql: added mk_set_search_path() (which uses the current search_path)
- 02:08 PM Revision 12462: schemas/util.sql: diff(text, text): in the outputted FULL JOIN query, documented that you should refer to the EXPLAIN output for the expansion of %==
- 01:54 PM Revision 12461: bugfix: schemas/util.sql: first_word(): ignore leading whitespace: need to use util.ltrim_nl(), as ltrim() only removes spaces
- 01:46 PM Revision 12460: bugfix: schemas/util.sql: first_word(): need to ignore leading whitespace. this applies to many of our queries, which have a leading newline.
- 01:44 PM Revision 12459: schemas/util.sql: explain(): don't debug-print the EXPLAIN, to avoid cluttering up the debug output
- 01:41 PM Revision 12458: schemas/util.sql: eval2set(): make debug-printing optional, for use with internal statements
- 01:28 PM Revision 12457: schemas/util.sql: added is_explain()
- 01:25 PM Revision 12456: fix: schemas/util.sql: diff(text, text): FULL JOIN SELECT statement: don't put a comment at the very beginning, because this prevents it from being autoexplained (this is the query we particularly want EXPLAIN output for)
- 01:22 PM Revision 12455: fix: schemas/util.sql: debug_print_sql(): only surround EXPLAIN output with newlines if there is any
- 01:19 PM Revision 12454: fix: schemas/util.sql: debug_print_sql(): need newline before and after EXPLAIN output to visually separate it from other debug info
- 01:06 PM Revision 12453: schemas/util.sql: util.debug_print_sql(): include the EXPLAIN output of applicable queries, for easier query plan debugging (just like lib/sql.py's db.autoexplain)
- 01:02 PM Revision 12452: schemas/util.sql: added explain2notice_msg_if_can()
- 12:57 PM Revision 12451: schemas/util.sql: added is_explainable(sql)
- 12:55 PM Revision 12450: schemas/util.sql: added first_word()
- 12:25 PM Revision 12449: schemas/util.sql: explain2notice(): use util.raise_notice()
- 12:23 PM Revision 12448: schemas/util.sql: explain2notice(): added helper function explain2notice_msg() which can also be used individually
- 12:21 PM Revision 12447: fix: schemas/util.sql: schema comment: noted that avoiding use of STRICT also makes functions *much* easier to troubleshoot, because they won't mysteriously do nothing if called with only NULL arguments, even when you have added debug-print statements
- 12:19 PM Revision 12446: schemas/util.sql, vegbien.sql: do not declare *any* SQL-language functions as STRICT, because this prevents them from being inlined (as documented in the util schema comment). the STRICT qualifier has been preserved where its behavior is necessary, rather than just used for optimization.
- 12:09 PM Revision 12445: fix: schemas/util.sql: schema comment: indicate that *all* SQL-language functions should never be declared STRICT, not just IMMUTABLE ones, since inlining is not limited to IMMUTABLE (or STABLE) functions (although constant folding is)
- 12:07 PM Revision 12444: bugfix: schemas/util.sql: join_strs_transform(): added back STRICT qualifier because it must be declared STRICT to use the special handling of STRICT aggregating functions
- 11:56 AM Revision 12443: schemas/util.sql: %==(anyelement, anyelement): needs to be declared STABLE instead of IMMUTABLE because it depends on the search_path (as described at http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)
- 11:49 AM Revision 12442: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: difference between STABLE and IMMUTABLE: functions that depend on the search_path: clarified that the confusing effects of using IMMUTABLE for one of these functions are only noticeable if the function is called on only *constant* values in a *PL/pgSQL* function (in which case the wrong search_path (the one in effect at *create* time) will be used)
- 11:37 AM Revision 12441: 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)
- 11:23 AM Revision 12440: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added explanation of the difference between STABLE and IMMUTABLE, and common pitfalls in using IMMUTABLE when you should use STABLE. svn: added troubleshooting steps for running `svn upgrade` after the last Mac system update, which upgrades svn.
- 10:55 AM Revision 12439: fix: schemas/util.sql: diff(text, text): use util.typeof() instead of pg_typeof() so the outputted query is runnable in any search_path
- 10:50 AM Revision 12438: schemas/util.sql: added typeof(), which unlike pg_typeof() uses util.qual_name() to create a search_path-independent name
- 10:33 AM Revision 12437: fix: schemas/util.sql: quote_typed(): schema-qualify the type so the expression does not depend on the search_path
- 10:31 AM Revision 12436: schemas/util.sql: %== : use custom keys() function for the type, so that the user only has to get the keys from their value, not also write the comparison of those keys
- 10:29 AM Revision 12435: schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables
- 10:12 AM Revision 12434: schemas/util.sql: diff(regclass, regclass): support custom %== by including the col_type_null's schema in the search_path
- 10:07 AM Revision 12433: schemas/util.sql: debug_print_return_value(): don't '-encode the value by default, because text values usually look better without the '-escaping
- 09:55 AM Revision 12432: bugfix: schemas/util.sql: mk_set_search_path(): debug_print_return_value() needs to be on mk_set_search_path(text), not mk_search_path(VARIADIC text[])
- 09:45 AM Revision 12431: schemas/util.sql: mk_search_path(): use debug_print_return_value() because this function is used with EXECUTE rather than util.eval() (in order to affect the calling function), so the search_path would not otherwise be printed
- 09:39 AM Revision 12430: schemas/util.sql: added debug_print_return_value()
- 09:34 AM Revision 12429: schemas/util.sql: diff(text, text): support custom search_path because custom %== operators are usually not in the util schema
- 09:28 AM Revision 12428: schemas/util.sql: diff(text, text): renamed IN params to be different from OUT params to support LANGUAGE plpgsql
- 09:04 AM Revision 12427: bugfix: schemas/util.sql: diff(*): changed from STABLE to VOLATILE because these functions create tables
- 07:56 AM Revision 12426: /README.TXT: use full hostname for jupiter so the commands work outside of the NCEAS network as well
- 07:52 AM Revision 12425: schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: added operator %== to match the rows by project_name
- 07:42 AM Revision 12424: 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)
- 07:21 AM Revision 12423: schemas/util.sql: added operator %== , which returns whether the map-keys of the compared values are the same
- 07:14 AM Revision 12422: schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables
- 12:24 AM Revision 12421: 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
- 12:16 AM Revision 12420: schemas/util.sql: diff(): support custom = operator for the table type (to FULL JOIN on only some columns). this requires casting to the base type, to avoid an ambiguity with the default = operator for record.
02/24/2014
- 11:18 PM Revision 12419: schemas/util.sql: remake_diff_table(): use util.quote_typed() instead of hardcoding the type
- 11:17 PM Revision 12418: fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query
- 10:51 PM Revision 12417: fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query
- 05:16 PM Revision 12416: 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
- 04:52 PM Revision 12415: schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix
- 04:50 PM Revision 12414: schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source
- 04:43 PM Revision 12413: schemas/util.sql: added drop_relations_like()
- 03:26 PM Revision 12412: 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.)
- 02:34 PM Revision 12411: 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
- 02:33 PM Revision 12410: schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully
- 11:39 AM Revision 12409: web/links/index.htm: updated to Firefox bookmarks: Roundtable: virtual collaboration: annotated the linked pages
- 11:24 AM Revision 12408: web/links/index.htm: updated to Firefox bookmarks: Roundtable: added materials for virtual collaboration roundtable. Mac: added steps for customizing screen saver security settings beyond the options supported in the UI.
- 08:58 AM Revision 12407: inputs/SALVIAS/validations.sql: added Brad's comments from validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql
- 08:53 AM Revision 12406: added inputs/SALVIAS/validations*.sql
- 08:50 AM Revision 12405: bugfix: lib/common.Makefile: `svn add`: need to run with --force because the new version of svn in the latest Mac upgrade errors if the file is already under version control
- 08:37 AM Revision 12404: fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: renamed truncated views to match the output queries
- 12:30 AM Revision 12403: bugfix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: can't double-JOIN like in MySQL (`JOIN a JOIN b ON a_conds AND b_conds`), so split double-JOIN into two JOINs w/ own ON clauses
- 12:28 AM Revision 12402: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: removed `LIMIT 10` to match output queries
- 12:26 AM Revision 12401: fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: updated table names to match our renamings
- 12:25 AM Revision 12400: 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
- 12:24 AM Revision 12399: schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)
- 12:21 AM Revision 12398: bugfix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: can't double-JOIN like in MySQL (`JOIN a JOIN b ON a_conds AND b_conds`), so split double-JOIN into two JOINs w/ own ON clauses
- 12:16 AM Revision 12397: added validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql, modified from bien3_validations_salvias_db_original.sql using the steps at http://wiki.vegpath.org/Aggregating_validations_refactoring
02/23/2014
- 11:57 PM Revision 12396: fix: /README.TXT: use exact ssh command needed to connect to vegbiendev/jupiter (eg. `ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk`) instead of vaguely referring to "on vegbiendev"/"on jupiter"
- 11:28 PM Revision 12395: /README.TXT: Full database import: screen: run `unset TMOUT` first because it is most important, now that the remote servers have a TMOUT set for extra security
- 08:29 PM Revision 12394: bin/psql_verbose_vegbien: use \\ instead of \ inside '' because this is sh, not bash
- 08:26 PM Revision 12393: bin/psql_verbose_vegbien: changed prep-statement order to match lib/sh/db.sh psql()
- 08:26 PM Revision 12392: bin/psql_verbose_vegbien: use `\set VERBOSITY terse` to hide stack traces/DETAIL sections of error messages, like in lib/sh/db.sh psql()
- 08:11 PM Revision 12391: bin/make_analytical_db: added `public_validations.remake_diff_tables()`
- 08:03 PM Task #871 (New): grant bien_read access to validation result tables
- 08:02 PM Task #870 (New): grant bien_read access to publishable staging tables
- 07:57 PM Task #501 (Resolved): find out which datasources won't allow their data to be publicly accessible
- 07:44 PM Revision 12390: fix: schemas/vegbien.sql: public_validations.rename_query_view(): fixed usage
- 07:43 PM Revision 12389: schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources
- 07:40 PM Revision 12388: schemas/vegbien.sql: added validatable_datasources()
- 07:36 PM Revision 12387: schemas/vegbien.sql: added validatable_datasources()
- 07:31 PM Revision 12386: schemas/vegbien.sql: validation_views(): allow leaving out schema to match all datasources
- 07:23 PM Revision 12385: schemas/util.sql: show_views_like(): take a schema regexp like util.show_relations_like(), to allow matching views in all schemas
- 07:19 PM Revision 12384: schemas/util.sql: added str_equality_regexp(literal text)
- 07:01 PM Revision 12383: 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
- 06:50 PM Revision 12382: schemas/util.sql: util.remake_diff_table(): don't force-re-create type_table. this allows type_table to be shared between multiple diff tables.
- 06:00 PM Revision 12381: /README.TXT: to back up the version history: added steps to sync git to the local machine
- 05:55 PM Revision 12380: added validation/aggregating/traits/BIEN2_traits/_archive/bien3_validations_traits_complete.sql from Brad's e-mail
- 05:32 PM Revision 12379: 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
- 05:08 PM Revision 12378: schemas/util.sql: added curr_func(func text, schema_anchor anyelement)
- 03:50 PM Revision 12377: schemas/util.sql: removed unneeded regproc(regprocedure), regprocedure(regproc) because these can actually be directly cast to one another, not just via oid
- 03:47 PM Revision 12376: schemas/util.sql: added quote_func_call()
- 03:22 PM Revision 12375: schemas/util.sql: added regprocedure(text)
- 03:11 PM Revision 12374: schemas/util.sql: func2regproc*(): removed func2* so that this has the format of a cast function (which it is)
- 03:10 PM Revision 12373: schemas/util.sql: added func2regproc(regprocedure)
- 03:08 PM Revision 12372: schemas/util.sql: added func2regprocedure(regproc)
- 02:51 PM Revision 12371: schemas/util.sql: added quote_typed()
- 01:30 PM Revision 12370: schemas/util.sql: remake_diff_table(): use util.append_comment() instead of util.set_comment() to allow other functions to add comments to the diff table
- 01:18 PM Revision 12369: schemas/util.sql: added append_comment(regclass)
- 01:00 PM Revision 12368: schemas/util.sql: added comment(oid)
- 12:13 PM Revision 12367: 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.
- 12:08 PM Revision 12366: 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
- 12:04 PM Revision 12365: schemas/vegbien.sql: added query_view_relation_max_prefix_len()
- 12:03 PM Revision 12364: fix: schemas/util.sql: drop_relation(text), rename_relation(text, text): appended _esc to params that should be passed in escaped, and _name to those that shouldn't
- 12:00 PM Revision 12363: bugfix: schemas/util.sql: rename_relation(): need quote_ident() around to_
- 11:39 AM Revision 12362: bugfix: schemas/util.sql: truncated_prefixed_name_regexp(): need to match the entire string, so that the regexp can be used with util.replace_suffix()
- 11:37 AM Revision 12361: bugfix: schemas/util.sql: truncated_prefixed_name_regexp(): need to pass max_prefix_len to util.name_was_truncated() because although the name itself might not have been truncated, it might become truncated once prefixed
- 11:32 AM Revision 12360: schemas/util.sql: name_was_truncated(): support max_prefix_len param to determine truncation of something that will be prefixed
- 11:22 AM Revision 12359: fix: name_was_truncated(): should use util.namedatalen() instead of hardcoding the value
02/22/2014
- 08:35 PM Revision 12358: schemas/util.sql: replace_suffix(): support strings that have been truncated (eg. as a table name) due to the prepending of a prefix
- 08:32 PM Revision 12357: schemas/util.sql: added truncated_prefixed_name_regexp()
- 08:12 PM Revision 12356: schemas/util.sql: added rtrim_n()
- 07:58 PM Revision 12355: schemas/util.sql: added name_was_truncated()
- 07:56 PM Revision 12354: schemas/util.sql: added namedatalen()
- 07:21 PM Revision 12353: schemas/util.sql: drop_relation(regclass), rename_relation(regclass): use util.qual_name() instead of ::text so that the schema qualifier is always included in the debug SQL
- 07:18 PM Revision 12352: schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query
- 07:13 PM Revision 12351: schemas/vegbien.sql: added new_query_view_relation_name()
- 07:11 PM Revision 12350: schemas/util.sql: added replace_suffix()
- 06:47 PM Revision 12349: schemas/util.sql: added rename_relation(regclass/text, text)
- 06:23 PM Revision 12348: fix: schemas/util.sql: drop_relation(): documented that it is actually not idempotent, because the regclass will not be valid after the relation is dropped
- 06:21 PM Revision 12347: schemas/util.sql: drop_relation(relation text, type text): put type param first because it is usually constant
- 06:12 PM Revision 12346: schemas/util.sql: drop_table(), drop_view(): use drop_relation(relation text, type text)
- 06:10 PM Revision 12345: schemas/util.sql: drop_relation(regclass): use simpler drop_relation(relation text, type text)
- 06:06 PM Revision 12344: schemas/util.sql: added relation_type(regclass)
- 06:04 PM Revision 12343: schemas/util.sql: added drop_relation(relation text, type text)
- 06:01 PM Revision 12342: bugfix: schemas/util.sql: drop_relation(): need to pass force param to util.drop_*()
- 05:50 PM Revision 12341: schemas/util.sql: added relation_type_char(regclass)
- 05:50 PM Revision 12340: bugfix: schemas/util.sql: relation_type(character): must use "char" type for single character, as character actually allows multiple characters. corrected return type.
- 05:47 PM Revision 12339: schemas/util.sql: added relation_type(character)
- 04:04 PM Revision 12338: schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query
- 03:49 PM Revision 12337: 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
- 03:48 PM Revision 12336: fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views
- 03:35 PM Revision 12335: schemas/vegbien.sql: added public_validations.query_views()
- 03:35 PM Revision 12334: schemas/util.sql: schema_matches(): should be IMMUTABLE, not STABLE, because it uses only parameter values
- 03:24 PM Revision 12333: schemas/util.sql: added regexp_quote()
- 03:18 PM Revision 12332: fix: schemas/util.sql: is_table(), is_view(): should be STABLE, not IMMUTABLE, because they reference tables
- 02:50 PM Revision 12331: schemas/util.sql: added drop_relation()
- 02:41 PM Revision 12330: schemas/util.sql: added is_table()
- 02:39 PM Revision 12329: schemas/util.sql: added is_view()
- 02:23 PM Revision 12328: bugfix: schemas/util.sql: show_views_like(): need to match the schema exactly, not via regexp like util.show_relations_like() does
- 02:14 PM Revision 12327: schemas/util.sql: show_views_like(): use util.show_relations_like()
- 02:03 PM Revision 12326: schemas/util.sql: show_views_like(): reversed argument order to match show_relations_like()
- 12:32 PM Revision 12325: schemas/util.sql: added show_relations_like()
- 12:21 PM Revision 12324: schemas/util.sql: added schema_matches()
02/21/2014
02/20/2014
- 07:18 PM Revision 12322: 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
- 07:12 PM Revision 12321: schemas/util.sql: materialize_query(): ANALYZE the created table so that it has the correct estimated rowcount
- 07:08 PM Revision 12320: schemas/util.sql: added analyze_()
- 06:49 PM Revision 12319: schemas/vegbien.sql: remake_diff_tables(): updated bien2_traits runtime (45 min) to include the now-properly-renamed views 6-8
- 06:14 PM Revision 12318: schemas/vegbien.sql: remake_diff_tables(): documented runtime for bien2_traits (30 min)
- 05:52 PM Revision 12317: fix: schemas/vegbien.sql: renamed output views to match input views
- 04:52 PM Revision 12316: schemas/vegbien.sql: _traits_01_count_records (output side): documented runtime (4 min)
- 04:44 PM Task #869 (New): enable schema changes to be made on vegbiendev rather than the development machine
- h3. fix
allow refactoring the live public schema the same way as the live TNRS cache:
# rename constant tables ... - 04:33 PM Revision 12315: bugfix: schemas/vegbien.sql: source_by_shortname(): resolved ambiguous column references by renaming PL/pgSQL vars and adding table qualifiers
- 04:28 PM Revision 12314: bugfix: schemas/vegbien.sql: source_by_shortname(): changed from STABLE to VOLATILE because the search_path can only be set in VOLATILE functions
- 04:19 PM Revision 12313: 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
- 08:42 AM Revision 12312: 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().
- 08:41 AM Revision 12311: schemas/util.sql: added raise_error_notice()
02/19/2014
02/18/2014
- 07:16 AM Revision 12309: schemas/vegbien.sql: added remake_diff_tables()
- 05:37 AM Revision 12308: bugfix: schemas/util.sql: diff(text, text): also need to include only different rows when comparing single-row tables
02/17/2014
- 09:06 PM Revision 12307: schemas/vegbien.sql: added validation_views()
- 08:59 PM Revision 12306: schemas/util.sql: show_views_like(): added predictable ordering
- 08:54 PM Revision 12305: schemas/util.sql: added show_views_like()
- 08:27 PM Revision 12304: schemas/util.sql: added schema_oid()
- 06:45 PM Revision 12303: schemas/util.sql: remake_diff_table(): add comment on the diff table showing how to view it in human-readable form. (the table unfortunately cannot be *materialized* in human-readable form, because this would create column name collisions between the two sides.)
- 06:38 PM Revision 12302: schemas/util.sql: added set_comment()
- 06:25 PM Revision 12301: schemas/util.sql: added eval2records()
- 05:48 PM Revision 12300: fix: schemas/util.sql: diff(regclass, regclass): display single row tables side-by-side
- 05:46 PM Revision 12299: schemas/util.sql: diff(text, text): implemented the single_row feature
- 05:40 PM Revision 12298: schemas/util.sql: diff(text, text): added single_row param (not implemented yet, to make reading the svn diff easier). to view this svn diff, it is helpful to use WinMerge, which offers moved block detection (/README.TXT > WinMerge setup).
- 04:54 PM Revision 12297: fix: schemas/util.sql: remake_diff_table(): util.diff() call: include explicit types on arguments to ensure that the correct diff() variant is used
- 04:32 PM Revision 12296: schemas/util.sql: added has_single_row()
- 04:08 PM Revision 12295: schemas/vegbien.sql: remake_diff_table(): prepend ~ to the type table so that it sorts at the end, away from the main tables
- 03:56 PM Revision 12294: 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().
- 03:55 PM Revision 12293: schemas/util.sql: added remake_diff_table()
- 03:51 PM Revision 12292: schemas/util.sql: drop_*(): added force param to use CASCADE mode
- 03:28 PM Revision 12291: fix: schemas/util.sql: diff(regclass, regclass): usage: col_type_null must actually be a shared base type of the tables
- 03:26 PM Revision 12290: fix: schemas/util.sql: diff(regclass, regclass): usage: text arguments must be cast to regclass to bind the right variant of diff()
- 03:24 PM Revision 12289: fix: schemas/util.sql: diff(regclass, regclass): renamed params to *_table because these actually can't be views, since views don't support inheritance
- 03:03 PM Revision 12288: schemas/util.sql: added copy_struct()
- 02:06 PM Revision 12287: schemas/vegbien.sql: added remake_diff_table()
- 01:58 PM Revision 12286: bugfix: inputs/bien2_traits/validations.sql: _traits_01_count_records: changed column names to match public_validations._traits_01_count_records
- 01:54 PM Revision 12285: schemas/util.sql: added inherit()
- 01:46 PM Revision 12284: bugfix: schemas/util.sql: diff(): need to explicitly cast each side to the return type because this does not happen automatically even when an implicit cast is available
- 01:32 PM Revision 12283: schemas/util.sql: renamed diff_views(), diff_any() to diff(...) because they are overloads of the same operation
- 01:22 PM Revision 12282: schemas/util.sql: diff_any(): documented how to run EXPLAIN on the FULL JOIN query
- 01:19 PM Revision 12281: schemas/util.sql: removed non-useful diff_queries() (it is not possible to match distinct `record` types to a common return type, so this would not have worked). use diff_views() instead.
- 01:14 PM Revision 12280: bugfix: schemas/util.sql: diff_queries(), diff_views(): embed views verbatim as FROM items, so that the type of their corresponding columns is the view's rowtype. this is necessary in order to match the types of the columns to the return type (set by col_type_null).
- 12:52 PM Revision 12279: schemas/util.sql: diff_cols(): renamed to diff_queries() because multiple columns are supported
- 12:26 PM Revision 12278: fix: schemas/util.sql: diff_cols(): don't assume that each side will have one column, as many of our validations queries have multiple columns
- 11:45 AM Revision 12277: bugfix: schemas/util.sql: diff_views(): need to use $2, not $1, for the 2nd query
- 11:40 AM Revision 12276: fix: schemas/util.sql: materialize_query(): remove any extra blank line before the query, to improve readability when newlines are used as a log message separator and are therefore confusing in the middle of a log message
- 11:37 AM Revision 12275: schemas/util.sql: added ltrim_nl()
- 11:25 AM Revision 12274: 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)
- 11:14 AM Revision 12273: 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)
- 11:05 AM Revision 12272: schemas/util.sql: added mk_set_search_path(VARIADIC schemas text[])
- 10:54 AM Revision 12271: schemas/util.sql: added mk_use_own_schema()
- 10:52 AM Revision 12270: schemas/util.sql: added mk_set_search_path()
- 10:17 AM Revision 12269: schemas/util.sql: use builtin string_agg(..., ) instead of concat()
- 12:34 AM Revision 12268: schemas/util.sql: added qual_name(type unknown) to avoid needing to explicitly specify the type of an input string
- 12:31 AM Revision 12267: schemas/util.sql: qual_name(table_ regclass): use ::text with search_path=pg_temp like qual_name(type regtype), rather than casting to text and then using qual_name(type regtype)
- 12:25 AM Revision 12266: schemas/util.sql: type_qual_name(type regtype): renamed to qual_name(type regtype) to match qual_name(table_ regclass)
- 12:17 AM Revision 12265: schemas/util.sql: added rematerialize_query()
02/16/2014
- 11:59 PM Revision 12264: schemas/vegbien.sql: added rematerialize_in_view()
- 11:16 PM Revision 12263: 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
- 11:08 PM Revision 12262: schemas/vegbien.sql: materialize_query(): support schema-qualified tables. this requires escaping tables in calls to this function.
- 10:52 PM Revision 12261: schemas/vegbien.sql: added rematerialize_out_view(in_view regclass)
- 10:45 PM Revision 12260: schemas/util.sql: added qual_name()
- 09:47 PM Revision 12259: schemas/vegbien.sql: added rematerialize_out_view(datasource_schema text, out_view regclass)
- 09:39 PM Revision 12258: 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
- 09:20 PM Revision 12257: 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)
- 09:14 PM Revision 12256: 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)
- 08:55 PM Revision 12255: schemas/vegbien.sql: schema_null param: renamed to schema_anchor to clarify what this is for
- 08:52 PM Revision 12254: fix: schemas/vegbien.sql: schema_null: clarified that this should *always* be left as the default value, not just usually
- 08:49 PM Revision 12253: 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
- 08:46 PM Revision 12252: schemas/vegbien.sql: public: added schema_anchor (used with schema_null params)
- 07:54 PM Revision 12251: schemas/util.sql: use new util.debug_print_sql() instead of `RAISE NOTICE` to improve readability of the debug output (which is used throughout the runscripts)
- 07:41 PM Revision 12250: schemas/util.sql: added debug_print_sql()
- 07:31 PM Revision 12249: schemas/util.sql: added raise_notice(), to allow NOTICEs to be raised in sql-language functions
- 05:56 PM Revision 12248: schemas/vegbien.sql: removed no longer used concat() aggregate. use builtin string_agg() instead.
- 05:55 PM Revision 12247: schemas/util.sql: added rematerialize_view()
- 04:22 PM Revision 12246: bugfix: inputs/bien2_traits/validations.sql: use a wrapper function for util.ifnull() so that the views don't get dropped when the util schema is reinstalled
- 03:24 PM Revision 12245: schemas/util.sql: schema(regtype), schema(regclass): use new schema(pg_namespace_oid oid)
- 03:22 PM Revision 12244: schemas/util.sql: added schema(pg_namespace_oid oid)
- 03:15 PM Revision 12243: schemas/util.sql: schema(regclass): use joins instead of nested SELECTs for consistency with schema(regtype)
- 03:08 PM Revision 12242: schemas/util.sql: regenerated from DB, which reorders the functions
- 02:44 PM Revision 12241: schemas/util.sql: table_name(): renamed to name(regclass) for consistency with other name(...) functions
- 02:28 PM Revision 12240: schemas/util.sql: table_schema(): renamed to schema(regclass) for consistency with other schema(...) functions
- 01:29 PM Revision 12239: web/links/index.htm: updated to Firefox bookmarks: Redmine: added links to the REST API for exporting data. svn: added instructions for using svnsync to export a repository. PostgreSQL: removed duplicate pages that were linked for multiple versions of Postgres.
- 06:45 AM Revision 12238: schemas/util.sql: schema_ident(): renamed to schema_esc() for clarity
- 06:42 AM Revision 12237: schemas/vegbien.sql: added schema_anchor, for use with schema_null params
- 06:39 AM Revision 12236: schemas/util.sql: added mk_search_path()
- 02:14 AM Revision 12235: 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)
02/15/2014
- 03:51 PM Revision 12234: schemas/util.sql: added materialize_view()
- 03:46 PM Revision 12233: schemas/util.sql: added table_name()
- 03:45 PM Revision 12232: schemas/util.sql: table_schema(): removed unneeded STRICT, which prevents inlining
- 03:41 PM Revision 12231: fix: schemas/util.sql: fix_array(): removed no longer accurate comment about effects of STRICT, which is no longer used
- 03:28 PM Revision 12230: fix: schemas/util.sql: materialize_query(): 1st param should be named table_, not view_
- 02:50 PM Revision 12229: schemas/util.sql: added drop_view()
- 02:46 PM Revision 12228: schemas/util.sql: added materialize_query()
- 04:08 AM Revision 12227: /README.TXT: Schema changes: clarified that the staging tables should only be reinstalled if needed
- 04:08 AM Revision 12226: /README.TXT: put **...** around all uppercased text, for consistency
02/14/2014
- 04:36 PM Revision 12225: _license/non-open-source/applies_to.txt: clarified that this is an exclusions list, not an inclusions list, in terms of what's open-source
- 03:09 PM Revision 12224: 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.
- 02:34 PM Revision 12223: validation/aggregating/*/*.sql: renamed views to match the current naming scheme in the DB. these files still need to be kept up-to-date because there are some queries that haven't been implemented yet, and therefore exist only in these files, not the DB.
- 12:39 PM Revision 12222: schemas/util.sql: ifnull(): use COALESCE() instead, because it turns out that MySQL's IFNULL() is just a special case of this
- 12:20 PM Revision 12221: added inputs/bien2_traits/validations.sql, from validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql
- 12:20 PM Revision 12220: inputs/input.Makefile: $(svnFilesGlob): added validations.sql
- 12:03 PM Revision 12219: bugfix: validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql: added util to search_path so that IFNULL() would link to util.ifnull()
- 11:55 AM Revision 12218: schemas/util.sql: added ifnull(), equivalent to MySQL's IFNULL() (Postgres auto-lowercases the name)
- 11:40 AM Revision 12217: lib/sh/util.sh: stderr_matches(): documented that the purpose of not redirecting fd 2 back to fd 2 is to allow log-filtering out an otherwise-confusing benign error
- 11:30 AM Revision 12216: lib/sh/util.sh: ignore_err_msg(): documented that unlike `|| true`, this suppresses only errors caused by a particular error *message*, rather than all error exit statuses
- 11:28 AM Revision 12215: bugfix: lib/runscripts/validations.pg.sql.run: updated table match pattern to include the type prefix that validations queries now contain
- 11:16 AM Revision 12214: bugfix: lib/runscripts/validations.pg.sql.run: --table: need to include explicit schema so that matching tables from other schemas are not included
- 11:00 AM Revision 12213: added inputs/bien2_traits/validations.sql.run
- 11:00 AM Revision 12212: added lib/runscripts/validations.pg.sql.run
- 10:59 AM Revision 12211: lib/sh/util.sh: stdout_contains(): usage: documented that this requires a `{ ... } 41>&1` wrapper
- 10:57 AM Revision 12210: lib/sh/util.sh: stderr2stdout(): clarified that fd 2 is *not* redirected back to fd 2
- 10:55 AM Revision 12209: bugfix: lib/sh/util.sh: stderr_matches(): need to avoid redirecting stderr and stdout to the same place, because this prevents redirecting stdout back to the original stdout after stderr has been filtered using |
- 10:23 AM Revision 12208: lib/sh/util.sh: local_export_array: renamed to just export_array because this was a replacement for export, not local_export
- 10:21 AM Revision 12207: lib/sh/util.sh: local_*array: don't need -a because that it's an array is autodetected by the ()
- 10:20 AM Revision 12206: lib/sh/util.sh: stderr_matches(): actually don't need to declare PIPESTATUS_ in a separate command, because local does support arrays
- 10:18 AM Revision 12205: lib/sh/util.sh: added local_export_array
- 10:17 AM Revision 12204: fix: lib/sh/util.sh: local_array: clarified that this is only needed for older versions of bash (the lack of support for arrays has apparently been fixed)
- 10:12 AM Revision 12203: lib/sh/util.sh: added local_array
- 10:07 AM Revision 12202: fix: lib/sh/util.sh: stderr_matches(): usage: documented that now need to manually rethrow any command error, if applicable
- 10:04 AM Revision 12201: *{.sh,run}: stderr_matches calls: don't need to wrap the command in `"try"` because stderr_matches now does this
- 09:55 AM Revision 12200: fix: lib/sh/util.sh: `shopt -s lastpipe`: suppress error message if not supported
- 09:50 AM Revision 12199: lib/sh/util.sh: ignore_err_msg(): use new stderr_matches alias, which includes prep_try (requires loading new aliases)
- 09:49 AM Revision 12198: bugfix: lib/sh/util.sh: stderr_matches(): need to save PIPESTATUS and then use the saved var because it's reset after each cmd
- 09:39 AM Revision 12197: lib/sh/util.sh: stderr_matches(): place exit status of cmd in $e for use with exception handling
- 09:21 AM Revision 12196: lib/sh/util.sh: set `shopt -s lastpipe`, to allow setting vars in the last command of a pipeline
- 08:43 AM Revision 12195: bugfix: lib/sh/util.sh: ignore_err_msg(): use `try` properly with prep_try and `"try"`
- 08:39 AM Revision 12194: lib/sh/util.sh: added prep_try and use it in try alias. try: documented how to run it with a wrapper command.
- 08:21 AM Revision 12193: lib/sh/db.sh: pg_dump(): ignore "No matching tables were found" error, using new ignore_err_msg()
- 08:20 AM Revision 12192: lib/sh/util.sh: added ignore_err_msg()
- 07:34 AM Revision 12191: lib/sh/db.sh: pg_schema_exists(): documented that `try` is used to suppress the error exit status
- 07:31 AM Revision 12190: lib/sh/util.sh: stderr_matches(): usage: surrounded command in if statement to indicate what context it would usually be used in
02/13/2014
- 05:01 PM Revision 12189: lib/runscripts/file.pg.sql.run, schema.pg.sql.run: support custom options to pg_dump in $@
- 04:10 PM Revision 12188: validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql: added view names using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
- 04:10 PM Revision 12187: validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql: removed trailing whitespace
- 04:06 PM Revision 12186: 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
- 02:23 PM Revision 12185: schemas/util.sql: diff_cols(): documented how to run EXPLAIN on the FULL JOIN query
- 02:14 PM Revision 12184: schemas/util.sql: diff_cols(): embed left_query/right_query directly in the main SQL statement, so that the query planner can take the sort order of the queries into account in planning the FULL JOIN (and where possible, use a fast merge join)
- 02:09 PM Revision 12183: schemas/vegbien.sql: regenerated from DB, which adds an autogenerated column alias
- 02:06 PM Revision 12182: schemas/vegbien.sql, validation/aggregating/*/*.sql: renamed public_validation_* to plural public_validations_* since we refer to them as aggregating validation*s*
- 02:05 PM Revision 12181: schemas/util.sql: added eval2col_pair()
- 01:23 PM Revision 12180: web/.phpPgAdmin/.htaccess: support linking to a function
- 01:08 PM Revision 12179: web/.phpPgAdmin/.htaccess: support linking to the functions list
- 01:05 PM Revision 12178: web/.phpPgAdmin/.htaccess: support linking to a view
- 12:58 PM Revision 12177: web/.phpPgAdmin/.htaccess: allow linking directly to the views list of a schema
- 12:54 PM Revision 12176: added backups/vegbien.r12025.backup.md5
- 12:54 PM Revision 12175: web/.phpPgAdmin/.htaccess: refactored to build the URL incrementally, extracting path components 1st->last, instead of handling each # of path components as a separate case (which was more rigid and created unnecessary duplication)
- 12:53 PM Revision 12174: backups/TNRS.backup.md5: updated
- 11:47 AM Revision 12173: bugfix: web/.phpPgAdmin/.htaccess: path RewriteRules: need to go directly to the applicable tab, rather than going via redirect.php, because redirect.php will use the *last selected tab*, rather than the first tab for that section, leading to unexpected results when a non-default tab was previously selected
- 10:41 AM Revision 12172: schemas/vegbien.sql, validation/aggregating/*/*.sql: renamed public_validation_* to plural public_validations_* since we refer to them as aggregating validation*s*
- 10:33 AM Revision 12171: validation/aggregating/pipeline/aggregating_validations_pipeline.odg: made "diff tables" the same size as the other major labels
- 10:30 AM Revision 12170: validation/aggregating/pipeline/aggregating_validations_pipeline.odg: added CSVs export arrows and import process arrows, as decided in conference call
- 08:34 AM Revision 12169: schemas/vegbien.sql: public_validation_plots: added Brad's notes as comments
- 08:29 AM Revision 12168: schemas/vegbien.sql: public_validation_plots: populated queries from validation/aggregating/plots/bien3_validations_salvias_vegbien.sql
- 08:26 AM Revision 12167: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: _12_count_of_verbatim_taxa_per_plot_in_each_project: removed typo (in the original queries, not added by the refactoring)
- 08:23 AM Revision 12166: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: shortened view names to fit in the 63-char limit
- 08:20 AM Revision 12165: schemas/vegbien.sql: public_validation_traits: added Brad's notes as comments
- 08:17 AM Revision 12164: schemas/vegbien.sql: public_validation_traits: populated queries from validation/aggregating/traits/bien3_validations_traits_bien3.sql
- 08:02 AM Revision 12163: validation/aggregating/*/*.sql: use "" to preserve case in column aliases, so that they match the input queries
- 07:58 AM Revision 12162: validation/aggregating/*/*.sql: use current_schema instead of a hardcoded datasource name or psql variable
- 07:49 AM Revision 12161: validation/aggregating/*/*.sql: search_path: added public
- 07:45 AM Revision 12160: validation/aggregating/*/*.sql: use the new type-specific validation schema names
- 07:41 AM Revision 12159: validation/aggregating/*/*.sql: removed type-specific prefix from the view name, which is now part of the validation schema
- 07:26 AM Revision 12158: inputs/import.stats.xls: updated import times
- 06:53 AM Revision 12157: inputs/import.stats.xls: updated import times
- 05:58 AM Revision 12156: schemas/vegbien.sql: analytical_stem.datasource: removed fkey to source.shortname because this prevents individually-reloaded datasources from being published
- 05:56 AM Revision 12155: schemas/vegbien.sql: publish(), rm(): documented runtime (1 min)
- 05:49 AM Revision 12154: schemas/vegbien.sql: added datasource_unpublish()
- 03:48 AM Revision 12153: lib/sql_io.py: automatic handling of input/output column type mismatches: also do this for identifying columns, which first cause an error in a join in sql.distinct_table() rather than in the main insert (and thus were not handled by the existing error handling). previously, the user would have had to manually cast the input column in postprocess.sql. this involves getting handle_MissingCastException() to update join_cols as well as mapping.
- 03:37 AM Revision 12152: lib/sql.py: distinct_table(): don't cache the table creation, because this prevents different distinct_tables from being created for the same input table
- 03:36 AM Revision 12151: lib/sql.py: create_table(), copy_table_struct(): support custom query options, such as cacheable
- 12:29 AM Revision 12150: lib/sql_io.py: put_table(): main loop MissingCastException handler: factored out into nested function so that it can also be used elsewhere
- 12:01 AM Revision 12149: lib/sql.py: parse_exception(): parse "operator does not exist" errors as MissingCastExceptions (these appear when a staging table column is of the wrong type)
Also available in: Atom