Project

General

Profile

Statistics
| Revision:
  • svn:ignore: *.bak *.log

# Date Author Comment
12474 02/25/2014 08:13 PM Aaron Marcuse-Kubitza

schemas/util.sql: debug_print_sql(): use runnable_sql()

12473 02/25/2014 08:12 PM Aaron Marcuse-Kubitza

schemas/util.sql: added runnable_sql()

12472 02/25/2014 08:07 PM Aaron Marcuse-Kubitza

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.

12471 02/25/2014 06:03 PM Aaron Marcuse-Kubitza

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.

12470 02/25/2014 05:40 PM Aaron Marcuse-Kubitza

schemas/util.sql: materialize_query(): add a comment on the table with the query it was generated from

12469 02/25/2014 05:39 PM Aaron Marcuse-Kubitza

schemas/util.sql: debug_print_sql(): util.mk_set_search_path(): use for_printing := true to comment out LOCAL

12468 02/25/2014 04:11 PM Aaron Marcuse-Kubitza

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)

12467 02/25/2014 04:09 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_set_search_path(): added for_printing option like for mk_set_search_path(text)

12466 02/25/2014 04:04 PM Aaron Marcuse-Kubitza

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)

12465 02/25/2014 02:48 PM Aaron Marcuse-Kubitza

schemas/util.sql: debug_print_sql(): include the search_path in case the query contains search_path-dependent elements (such as operators)

12464 02/25/2014 02:45 PM Aaron Marcuse-Kubitza

schemas/util.sql: util.explain2notice_msg(): add newline before and after to visually separate it from other debug info

12463 02/25/2014 02:30 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_set_search_path() (which uses the current search_path)

12462 02/25/2014 02:08 PM Aaron Marcuse-Kubitza

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 %==

12461 02/25/2014 01:54 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: first_word(): ignore leading whitespace: need to use util.ltrim_nl(), as ltrim() only removes spaces

12460 02/25/2014 01:46 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: first_word(): need to ignore leading whitespace. this applies to many of our queries, which have a leading newline.

12459 02/25/2014 01:44 PM Aaron Marcuse-Kubitza

schemas/util.sql: explain(): don't debug-print the EXPLAIN, to avoid cluttering up the debug output

12458 02/25/2014 01:41 PM Aaron Marcuse-Kubitza

schemas/util.sql: eval2set(): make debug-printing optional, for use with internal statements

12457 02/25/2014 01:28 PM Aaron Marcuse-Kubitza

schemas/util.sql: added is_explain()

12456 02/25/2014 01:25 PM Aaron Marcuse-Kubitza

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)

12455 02/25/2014 01:22 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: debug_print_sql(): only surround EXPLAIN output with newlines if there is any

12454 02/25/2014 01:19 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: debug_print_sql(): need newline before and after EXPLAIN output to visually separate it from other debug info

12453 02/25/2014 01:06 PM Aaron Marcuse-Kubitza

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)

12452 02/25/2014 01:02 PM Aaron Marcuse-Kubitza

schemas/util.sql: added explain2notice_msg_if_can()

12451 02/25/2014 12:57 PM Aaron Marcuse-Kubitza

schemas/util.sql: added is_explainable(sql)

12450 02/25/2014 12:55 PM Aaron Marcuse-Kubitza

schemas/util.sql: added first_word()

12449 02/25/2014 12:25 PM Aaron Marcuse-Kubitza

schemas/util.sql: explain2notice(): use util.raise_notice()

12448 02/25/2014 12:23 PM Aaron Marcuse-Kubitza

schemas/util.sql: explain2notice(): added helper function explain2notice_msg() which can also be used individually

12447 02/25/2014 12:21 PM Aaron Marcuse-Kubitza

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

12446 02/25/2014 12:19 PM Aaron Marcuse-Kubitza

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.

12445 02/25/2014 12:09 PM Aaron Marcuse-Kubitza

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)

12444 02/25/2014 12:07 PM Aaron Marcuse-Kubitza

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

12443 02/25/2014 11:56 AM Aaron Marcuse-Kubitza

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

12441 02/25/2014 11:37 AM Aaron Marcuse-Kubitza

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)

12439 02/25/2014 10:55 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: diff(text, text): use util.typeof() instead of pg_typeof() so the outputted query is runnable in any search_path

12438 02/25/2014 10:50 AM Aaron Marcuse-Kubitza

schemas/util.sql: added typeof(), which unlike pg_typeof() uses util.qual_name() to create a search_path-independent name

12437 02/25/2014 10:33 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: quote_typed(): schema-qualify the type so the expression does not depend on the search_path

12436 02/25/2014 10:31 AM Aaron Marcuse-Kubitza

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

12435 02/25/2014 10:29 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

12434 02/25/2014 10:12 AM Aaron Marcuse-Kubitza

schemas/util.sql: diff(regclass, regclass): support custom %== by including the col_type_null's schema in the search_path

12433 02/25/2014 10:07 AM Aaron Marcuse-Kubitza

schemas/util.sql: debug_print_return_value(): don't '-encode the value by default, because text values usually look better without the '-escaping

12432 02/25/2014 09:55 AM Aaron Marcuse-Kubitza

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[])

12431 02/25/2014 09:45 AM Aaron Marcuse-Kubitza

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

12430 02/25/2014 09:39 AM Aaron Marcuse-Kubitza

schemas/util.sql: added debug_print_return_value()

12429 02/25/2014 09:34 AM Aaron Marcuse-Kubitza

schemas/util.sql: diff(text, text): support custom search_path because custom %== operators are usually not in the util schema

12428 02/25/2014 09:28 AM Aaron Marcuse-Kubitza

schemas/util.sql: diff(text, text): renamed IN params to be different from OUT params to support LANGUAGE plpgsql

12427 02/25/2014 09:04 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: diff(*): changed from STABLE to VOLATILE because these functions create tables

12425 02/25/2014 07:52 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: added operator %== to match the rows by project_name

12424 02/25/2014 07:42 AM Aaron Marcuse-Kubitza

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)

12423 02/25/2014 07:21 AM Aaron Marcuse-Kubitza

schemas/util.sql: added operator %== , which returns whether the map-keys of the compared values are the same

12422 02/25/2014 07:14 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

12421 02/25/2014 12:24 AM Aaron Marcuse-Kubitza

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

12420 02/25/2014 12:16 AM Aaron Marcuse-Kubitza

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.

12419 02/24/2014 11:18 PM Aaron Marcuse-Kubitza

schemas/util.sql: remake_diff_table(): use util.quote_typed() instead of hardcoding the type

12418 02/24/2014 11:17 PM Aaron Marcuse-Kubitza

fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query

12416 02/24/2014 05:16 PM Aaron Marcuse-Kubitza

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

12415 02/24/2014 04:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix

12414 02/24/2014 04:50 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source

12413 02/24/2014 04:43 PM Aaron Marcuse-Kubitza

schemas/util.sql: added drop_relations_like()

12412 02/24/2014 03:26 PM Aaron Marcuse-Kubitza

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

12411 02/24/2014 02:34 PM Aaron Marcuse-Kubitza

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

12410 02/24/2014 02:33 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully

12400 02/24/2014 12:25 AM Aaron Marcuse-Kubitza

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

12399 02/24/2014 12:24 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)

12390 02/23/2014 07:44 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: public_validations.rename_query_view(): fixed usage

12389 02/23/2014 07:43 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources

12388 02/23/2014 07:40 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added validatable_datasources()

12387 02/23/2014 07:36 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added validatable_datasources()

12386 02/23/2014 07:31 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: validation_views(): allow leaving out schema to match all datasources

12385 02/23/2014 07:23 PM Aaron Marcuse-Kubitza

schemas/util.sql: show_views_like(): take a schema regexp like util.show_relations_like(), to allow matching views in all schemas

12384 02/23/2014 07:19 PM Aaron Marcuse-Kubitza

schemas/util.sql: added str_equality_regexp(literal text)

12383 02/23/2014 07:01 PM Aaron Marcuse-Kubitza

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

12382 02/23/2014 06:50 PM Aaron Marcuse-Kubitza

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.

12379 02/23/2014 05:32 PM Aaron Marcuse-Kubitza

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

12378 02/23/2014 05:08 PM Aaron Marcuse-Kubitza

schemas/util.sql: added curr_func(func text, schema_anchor anyelement)

12377 02/23/2014 03:50 PM Aaron Marcuse-Kubitza

schemas/util.sql: removed unneeded regproc(regprocedure), regprocedure(regproc) because these can actually be directly cast to one another, not just via oid

12376 02/23/2014 03:47 PM Aaron Marcuse-Kubitza

schemas/util.sql: added quote_func_call()

12375 02/23/2014 03:22 PM Aaron Marcuse-Kubitza

schemas/util.sql: added regprocedure(text)

12374 02/23/2014 03:11 PM Aaron Marcuse-Kubitza

schemas/util.sql: func2regproc*(): removed func2* so that this has the format of a cast function (which it is)

12373 02/23/2014 03:10 PM Aaron Marcuse-Kubitza

schemas/util.sql: added func2regproc(regprocedure)

12372 02/23/2014 03:08 PM Aaron Marcuse-Kubitza

schemas/util.sql: added func2regprocedure(regproc)

12371 02/23/2014 02:51 PM Aaron Marcuse-Kubitza

schemas/util.sql: added quote_typed()

12370 02/23/2014 01:30 PM Aaron Marcuse-Kubitza

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

12369 02/23/2014 01:18 PM Aaron Marcuse-Kubitza

schemas/util.sql: added append_comment(regclass)

12368 02/23/2014 01:00 PM Aaron Marcuse-Kubitza

schemas/util.sql: added comment(oid)

12367 02/23/2014 12:13 PM Aaron Marcuse-Kubitza

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.

12366 02/23/2014 12:08 PM Aaron Marcuse-Kubitza

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

12365 02/23/2014 12:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added query_view_relation_max_prefix_len()

12364 02/23/2014 12:03 PM Aaron Marcuse-Kubitza

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

12363 02/23/2014 12:00 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: rename_relation(): need quote_ident() around to_

12362 02/23/2014 11:39 AM Aaron Marcuse-Kubitza

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

12361 02/23/2014 11:37 AM Aaron Marcuse-Kubitza

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

12360 02/23/2014 11:32 AM Aaron Marcuse-Kubitza

schemas/util.sql: name_was_truncated(): support max_prefix_len param to determine truncation of something that will be prefixed

12359 02/23/2014 11:22 AM Aaron Marcuse-Kubitza

fix: name_was_truncated(): should use util.namedatalen() instead of hardcoding the value

12358 02/22/2014 08:35 PM Aaron Marcuse-Kubitza

schemas/util.sql: replace_suffix(): support strings that have been truncated (eg. as a table name) due to the prepending of a prefix

12357 02/22/2014 08:32 PM Aaron Marcuse-Kubitza

schemas/util.sql: added truncated_prefixed_name_regexp()

12356 02/22/2014 08:12 PM Aaron Marcuse-Kubitza

schemas/util.sql: added rtrim_n()

12355 02/22/2014 07:58 PM Aaron Marcuse-Kubitza

schemas/util.sql: added name_was_truncated()

12354 02/22/2014 07:56 PM Aaron Marcuse-Kubitza

schemas/util.sql: added namedatalen()

12353 02/22/2014 07:21 PM Aaron Marcuse-Kubitza

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

12352 02/22/2014 07:18 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query