Project

General

Profile

Statistics
| Revision:
  • svn:ignore: extern

# Date Author Comment
12674 03/13/2014 01:46 AM Aaron Marcuse-Kubitza

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

12673 03/13/2014 01:14 AM Aaron Marcuse-Kubitza

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

12672 03/13/2014 01:12 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: schema comment: documented how to reset the key and value columns for all validations queries

12671 03/12/2014 11:56 PM Aaron Marcuse-Kubitza

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

12670 03/12/2014 11:53 PM Aaron Marcuse-Kubitza

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())

12669 03/12/2014 11:49 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: rm_output_queries(): also need to include keys_* and values__* types, as these are also associated with the query

12668 03/12/2014 11:40 PM Aaron Marcuse-Kubitza

schemas/util.sql: added debug_print_func_call(text) and use it where applicable

12667 03/12/2014 11:33 PM Aaron Marcuse-Kubitza

schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match

12666 03/12/2014 06:26 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row

12665 03/12/2014 06:18 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: public_validations.validation_views(): need to include views with letters after the query # (eg. _plots_06a_list_of_stems)

12664 03/12/2014 05:41 PM Aaron Marcuse-Kubitza

schemas/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.

12663 03/12/2014 05:40 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if all tables have all 1s

12662 03/12/2014 05:38 PM Aaron Marcuse-Kubitza

schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if all tables have all 1s

12661 03/12/2014 05:36 PM Aaron Marcuse-Kubitza

schemas/util.sql: added freq_always_1(regclass[])

12660 03/12/2014 05:35 PM Aaron Marcuse-Kubitza

schemas/util.sql: added drop_column(regclass[])

12659 03/12/2014 05:04 PM Aaron Marcuse-Kubitza

schemas/util.sql: added parent(regclass)

12658 03/12/2014 04:48 PM Aaron Marcuse-Kubitza

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

12657 03/12/2014 04:44 PM Aaron Marcuse-Kubitza

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

12656 03/12/2014 04:13 PM Aaron Marcuse-Kubitza

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

12655 03/12/2014 04:04 PM Aaron Marcuse-Kubitza

schemas/util.sql: added auto_rm_freq(regclass)

12654 03/12/2014 03:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: added freq_always_1(regclass)

12653 03/12/2014 03:00 PM Aaron Marcuse-Kubitza

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().

12652 03/12/2014 02:44 PM Aaron Marcuse-Kubitza

schemas/util.sql: added to_freq(regclass)

12651 03/12/2014 02:43 PM Aaron Marcuse-Kubitza

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

12650 03/12/2014 01:11 PM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB

12649 03/12/2014 12:53 PM Aaron Marcuse-Kubitza

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

12648 03/12/2014 04:44 AM Aaron Marcuse-Kubitza

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

12647 03/12/2014 04:41 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj

12646 03/12/2014 03:44 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB

12645 03/12/2014 03:44 AM Aaron Marcuse-Kubitza

fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier

12644 03/12/2014 03:35 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_09_list_of_plots_which_use_line_intercept

12643 03/12/2014 03:20 AM Aaron Marcuse-Kubitza

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

12642 03/12/2014 03:04 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_08_list_of_plots_which_use_percent_cover

12641 03/12/2014 12:04 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB

12640 03/12/2014 12:01 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_07_list_of_plots_which_use_counts_of_indiv_per_species

12639 03/11/2014 09:57 PM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB

12638 03/11/2014 09:56 PM Aaron Marcuse-Kubitza

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)

12637 03/11/2014 04:05 PM Aaron Marcuse-Kubitza

web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented how to prevent incorrect query plans (`SET enable_seqscan = off;`, etc.)

12636 03/11/2014 03:38 PM Aaron Marcuse-Kubitza

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:...

12635 03/07/2014 10:49 PM Aaron Marcuse-Kubitza

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

12634 03/07/2014 09:53 PM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB

12633 03/07/2014 09:50 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: plot: removed explicit column lists added in the autorename of plot.location_id->plot_id

12632 03/07/2014 09:41 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: plot: renamed pkey to plot_id. note that the field is autorenamed in all validation views which use it.

12631 03/07/2014 09:18 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location

12630 03/07/2014 08:55 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: locationevent: added missing fkey on place_visit_id

12629 03/07/2014 04:42 PM Aaron Marcuse-Kubitza

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.)

12628 03/07/2014 05:51 AM Aaron Marcuse-Kubitza

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

12627 03/07/2014 05:42 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB

12626 03/07/2014 05:35 AM Aaron Marcuse-Kubitza

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.

12625 03/07/2014 05:25 AM Aaron Marcuse-Kubitza

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).

12624 03/07/2014 05:23 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent: documented `CREATE INDEX locationevent_place_visit_id` runtime (3 min)

12623 03/07/2014 04:53 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries

12622 03/07/2014 02:26 AM Aaron Marcuse-Kubitza

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").

12621 03/06/2014 10:45 PM Aaron Marcuse-Kubitza

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).

12620 03/06/2014 10:26 PM Aaron Marcuse-Kubitza

schemas/postgresql.conf: log_min_messages: dropped the verbosity back down to the default, to avoid clogging up the logs

12619 03/06/2014 10:21 PM Aaron Marcuse-Kubitza

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

12618 03/06/2014 09:51 PM Aaron Marcuse-Kubitza

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

12617 03/06/2014 09:40 PM Aaron Marcuse-Kubitza

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)

12616 03/06/2014 09:37 PM Aaron Marcuse-Kubitza

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)

12615 03/06/2014 07:35 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: location: documented `CREATE INDEX plot_source_id` runtime (5 min)

12614 03/06/2014 07:30 PM Aaron Marcuse-Kubitza

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.

12613 03/06/2014 11:30 AM Aaron Marcuse-Kubitza

planning/meetings/BIEN conference call availability.xlsx: updated

12612 03/06/2014 11:06 AM Aaron Marcuse-Kubitza

bugfix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: need to escape the quotes in \set ... 'SALVIAS'

12611 03/06/2014 11:04 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `SET search_path TO public;` since this is the default

12610 03/06/2014 11:03 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: search_path: removed public_validations since we are not creating views

12609 03/06/2014 11:02 AM Aaron Marcuse-Kubitza

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

12608 03/06/2014 10:59 AM Aaron Marcuse-Kubitza

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

12607 03/06/2014 10:57 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB

12606 03/06/2014 08:57 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table

12605 03/06/2014 08:52 AM Aaron Marcuse-Kubitza

fix: inputs/SALVIAS/validations.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: changed columns to match output query

12604 03/06/2014 08:31 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table

12603 03/06/2014 08:29 AM Aaron Marcuse-Kubitza

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

12602 03/06/2014 08:14 AM Aaron Marcuse-Kubitza

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

12601 03/06/2014 08:12 AM Aaron Marcuse-Kubitza

bugfix: inputs/SALVIAS/validations.sql: _plots_10a_aggregate_observation_individual_counts: changed individual_id type to match output query

12600 03/06/2014 02:18 AM Aaron Marcuse-Kubitza

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.

12599 03/06/2014 01:59 AM Aaron Marcuse-Kubitza

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

12598 03/06/2014 12:33 AM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: _plots_10a_aggregate_observation_individual_counts: need to use taxonoccurrence.sourceaccessioncode, not aggregateoccurrence.sourceaccessioncode, because aggregateoccurrence.sourceaccessioncode is not populated

12597 03/06/2014 12:09 AM Aaron Marcuse-Kubitza

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())

12596 03/06/2014 12:07 AM Aaron Marcuse-Kubitza

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

12595 03/06/2014 12:00 AM Aaron Marcuse-Kubitza

schemas/util.sql: create_if_not_exists(): also support `CREATE FUNCTION` (by handling duplicate_function exceptions)

12594 03/05/2014 11:53 PM Aaron Marcuse-Kubitza

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)

12593 03/05/2014 01:01 PM Aaron Marcuse-Kubitza

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())

12592 03/05/2014 12:50 PM Aaron Marcuse-Kubitza

schemas/util.sql: added show_types_like()

12591 03/05/2014 12:42 PM Aaron Marcuse-Kubitza

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**)

12590 03/05/2014 12:25 PM Aaron Marcuse-Kubitza

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**)

12589 03/05/2014 12:13 PM Aaron Marcuse-Kubitza

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

12588 03/05/2014 07:28 AM Aaron Marcuse-Kubitza

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().

12587 03/05/2014 07:24 AM Aaron Marcuse-Kubitza

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

12586 03/05/2014 07:23 AM Aaron Marcuse-Kubitza

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

12585 03/05/2014 01:02 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.

12584 03/05/2014 12:59 AM Aaron Marcuse-Kubitza

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.

12583 03/05/2014 12:32 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger

12582 03/04/2014 05:56 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: public_validations.query_view_relation_max_prefix_len(): added values__ prefix

12581 03/04/2014 05:52 PM Aaron Marcuse-Kubitza

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

12580 03/04/2014 05:48 PM Aaron Marcuse-Kubitza

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

12579 03/04/2014 05:47 PM Aaron Marcuse-Kubitza

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

12578 03/04/2014 05:40 PM Aaron Marcuse-Kubitza

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

12577 03/04/2014 05:39 PM Aaron Marcuse-Kubitza

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

12576 03/04/2014 05:22 PM Aaron Marcuse-Kubitza

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

12575 03/04/2014 05:20 PM Aaron Marcuse-Kubitza

schemas/util.sql: added prefixed_name()