diff options
Diffstat (limited to 'src/test/regress/expected/guc.out')
| -rw-r--r-- | src/test/regress/expected/guc.out | 881 |
1 files changed, 881 insertions, 0 deletions
diff --git a/src/test/regress/expected/guc.out b/src/test/regress/expected/guc.out new file mode 100644 index 0000000..b1ea041 --- /dev/null +++ b/src/test/regress/expected/guc.out @@ -0,0 +1,881 @@ +-- pg_regress should ensure that this default value applies; however +-- we can't rely on any specific default value of vacuum_cost_delay +SHOW datestyle; + DateStyle +--------------- + Postgres, MDY +(1 row) + +-- SET to some nondefault value +SET vacuum_cost_delay TO 40; +SET datestyle = 'ISO, YMD'; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +-- SET LOCAL has no effect outside of a transaction +SET LOCAL vacuum_cost_delay TO 50; +WARNING: SET LOCAL can only be used in transaction blocks +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SET LOCAL datestyle = 'SQL'; +WARNING: SET LOCAL can only be used in transaction blocks +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +-- SET LOCAL within a transaction that commits +BEGIN; +SET LOCAL vacuum_cost_delay TO 50; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 50ms +(1 row) + +SET LOCAL datestyle = 'SQL'; +SHOW datestyle; + DateStyle +----------- + SQL, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------- + 08/13/2006 12:34:56 PDT +(1 row) + +COMMIT; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +-- SET should be reverted after ROLLBACK +BEGIN; +SET vacuum_cost_delay TO 60; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 60ms +(1 row) + +SET datestyle = 'German'; +SHOW datestyle; + DateStyle +------------- + German, DMY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------- + 13.08.2006 12:34:56 PDT +(1 row) + +ROLLBACK; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +-- Some tests with subtransactions +BEGIN; +SET vacuum_cost_delay TO 70; +SET datestyle = 'MDY'; +SHOW datestyle; + DateStyle +----------- + ISO, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +SAVEPOINT first_sp; +SET vacuum_cost_delay TO 80.1; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 80100us +(1 row) + +SET datestyle = 'German, DMY'; +SHOW datestyle; + DateStyle +------------- + German, DMY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------- + 13.08.2006 12:34:56 PDT +(1 row) + +ROLLBACK TO first_sp; +SHOW datestyle; + DateStyle +----------- + ISO, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +SAVEPOINT second_sp; +SET vacuum_cost_delay TO '900us'; +SET datestyle = 'SQL, YMD'; +SHOW datestyle; + DateStyle +----------- + SQL, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------- + 08/13/2006 12:34:56 PDT +(1 row) + +SAVEPOINT third_sp; +SET vacuum_cost_delay TO 100; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 100ms +(1 row) + +SET datestyle = 'Postgres, MDY'; +SHOW datestyle; + DateStyle +--------------- + Postgres, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------------ + Sun Aug 13 12:34:56 2006 PDT +(1 row) + +ROLLBACK TO third_sp; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 900us +(1 row) + +SHOW datestyle; + DateStyle +----------- + SQL, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------- + 08/13/2006 12:34:56 PDT +(1 row) + +ROLLBACK TO second_sp; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 70ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +ROLLBACK; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +-- SET LOCAL with Savepoints +BEGIN; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +SAVEPOINT sp; +SET LOCAL vacuum_cost_delay TO 30; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 30ms +(1 row) + +SET LOCAL datestyle = 'Postgres, MDY'; +SHOW datestyle; + DateStyle +--------------- + Postgres, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------------ + Sun Aug 13 12:34:56 2006 PDT +(1 row) + +ROLLBACK TO sp; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +ROLLBACK; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +-- SET LOCAL persists through RELEASE (which was not true in 8.0-8.2) +BEGIN; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +SAVEPOINT sp; +SET LOCAL vacuum_cost_delay TO 30; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 30ms +(1 row) + +SET LOCAL datestyle = 'Postgres, MDY'; +SHOW datestyle; + DateStyle +--------------- + Postgres, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------------ + Sun Aug 13 12:34:56 2006 PDT +(1 row) + +RELEASE SAVEPOINT sp; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 30ms +(1 row) + +SHOW datestyle; + DateStyle +--------------- + Postgres, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------------ + Sun Aug 13 12:34:56 2006 PDT +(1 row) + +ROLLBACK; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +-- SET followed by SET LOCAL +BEGIN; +SET vacuum_cost_delay TO 40; +SET LOCAL vacuum_cost_delay TO 50; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 50ms +(1 row) + +SET datestyle = 'ISO, DMY'; +SET LOCAL datestyle = 'Postgres, MDY'; +SHOW datestyle; + DateStyle +--------------- + Postgres, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------------ + Sun Aug 13 12:34:56 2006 PDT +(1 row) + +COMMIT; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 40ms +(1 row) + +SHOW datestyle; + DateStyle +----------- + ISO, DMY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +-- +-- Test RESET. We use datestyle because the reset value is forced by +-- pg_regress, so it doesn't depend on the installation's configuration. +-- +SET datestyle = iso, ymd; +SHOW datestyle; + DateStyle +----------- + ISO, YMD +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------ + 2006-08-13 12:34:56-07 +(1 row) + +RESET datestyle; +SHOW datestyle; + DateStyle +--------------- + Postgres, MDY +(1 row) + +SELECT '2006-08-13 12:34:56'::timestamptz; + timestamptz +------------------------------ + Sun Aug 13 12:34:56 2006 PDT +(1 row) + +-- Test some simple error cases +SET seq_page_cost TO 'NaN'; +ERROR: invalid value for parameter "seq_page_cost": "NaN" +SET vacuum_cost_delay TO '10s'; +ERROR: 10000 ms is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100) +SET no_such_variable TO 42; +ERROR: unrecognized configuration parameter "no_such_variable" +-- Test "custom" GUCs created on the fly (which aren't really an +-- intended feature, but many people use them). +SHOW custom.my_guc; -- error, not known yet +ERROR: unrecognized configuration parameter "custom.my_guc" +SET custom.my_guc = 42; +SHOW custom.my_guc; + custom.my_guc +--------------- + 42 +(1 row) + +RESET custom.my_guc; -- this makes it go to empty, not become unknown again +SHOW custom.my_guc; + custom.my_guc +--------------- + +(1 row) + +SET custom.my.qualified.guc = 'foo'; +SHOW custom.my.qualified.guc; + custom.my.qualified.guc +------------------------- + foo +(1 row) + +SET custom."bad-guc" = 42; -- disallowed because -c cannot set this name +ERROR: invalid configuration parameter name "custom.bad-guc" +DETAIL: Custom parameter names must be two or more simple identifiers separated by dots. +SHOW custom."bad-guc"; +ERROR: unrecognized configuration parameter "custom.bad-guc" +SET special."weird name" = 'foo'; -- could be allowed, but we choose not to +ERROR: invalid configuration parameter name "special.weird name" +DETAIL: Custom parameter names must be two or more simple identifiers separated by dots. +SHOW special."weird name"; +ERROR: unrecognized configuration parameter "special.weird name" +-- Check what happens when you try to set a "custom" GUC within the +-- namespace of an extension. +SET plpgsql.extra_foo_warnings = true; -- allowed if plpgsql is not loaded yet +LOAD 'plpgsql'; -- this will throw a warning and delete the variable +WARNING: invalid configuration parameter name "plpgsql.extra_foo_warnings", removing it +DETAIL: "plpgsql" is now a reserved prefix. +SET plpgsql.extra_foo_warnings = true; -- now, it's an error +ERROR: invalid configuration parameter name "plpgsql.extra_foo_warnings" +DETAIL: "plpgsql" is a reserved prefix. +SHOW plpgsql.extra_foo_warnings; +ERROR: unrecognized configuration parameter "plpgsql.extra_foo_warnings" +-- +-- Test DISCARD TEMP +-- +CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS; +SELECT relname FROM pg_class WHERE relname = 'reset_test'; + relname +------------ + reset_test +(1 row) + +DISCARD TEMP; +SELECT relname FROM pg_class WHERE relname = 'reset_test'; + relname +--------- +(0 rows) + +-- +-- Test DISCARD ALL +-- +-- do changes +DECLARE foo CURSOR WITH HOLD FOR SELECT 1; +PREPARE foo AS SELECT 1; +LISTEN foo_event; +SET vacuum_cost_delay = 13; +CREATE TEMP TABLE tmp_foo (data text) ON COMMIT DELETE ROWS; +CREATE ROLE regress_guc_user; +SET SESSION AUTHORIZATION regress_guc_user; +-- look changes +SELECT pg_listening_channels(); + pg_listening_channels +----------------------- + foo_event +(1 row) + +SELECT name FROM pg_prepared_statements; + name +------ + foo +(1 row) + +SELECT name FROM pg_cursors; + name +------ + foo +(1 row) + +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 13ms +(1 row) + +SELECT relname from pg_class where relname = 'tmp_foo'; + relname +--------- + tmp_foo +(1 row) + +SELECT current_user = 'regress_guc_user'; + ?column? +---------- + t +(1 row) + +-- discard everything +DISCARD ALL; +-- look again +SELECT pg_listening_channels(); + pg_listening_channels +----------------------- +(0 rows) + +SELECT name FROM pg_prepared_statements; + name +------ +(0 rows) + +SELECT name FROM pg_cursors; + name +------ +(0 rows) + +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 0 +(1 row) + +SELECT relname from pg_class where relname = 'tmp_foo'; + relname +--------- +(0 rows) + +SELECT current_user = 'regress_guc_user'; + ?column? +---------- + f +(1 row) + +DROP ROLE regress_guc_user; +-- +-- search_path should react to changes in pg_namespace +-- +set search_path = foo, public, not_there_initially; +select current_schemas(false); + current_schemas +----------------- + {public} +(1 row) + +create schema not_there_initially; +select current_schemas(false); + current_schemas +------------------------------ + {public,not_there_initially} +(1 row) + +drop schema not_there_initially; +select current_schemas(false); + current_schemas +----------------- + {public} +(1 row) + +reset search_path; +-- +-- Tests for function-local GUC settings +-- +set work_mem = '3MB'; +create function report_guc(text) returns text as +$$ select current_setting($1) $$ language sql +set work_mem = '1MB'; +select report_guc('work_mem'), current_setting('work_mem'); + report_guc | current_setting +------------+----------------- + 1MB | 3MB +(1 row) + +alter function report_guc(text) set work_mem = '2MB'; +select report_guc('work_mem'), current_setting('work_mem'); + report_guc | current_setting +------------+----------------- + 2MB | 3MB +(1 row) + +alter function report_guc(text) reset all; +select report_guc('work_mem'), current_setting('work_mem'); + report_guc | current_setting +------------+----------------- + 3MB | 3MB +(1 row) + +-- SET LOCAL is restricted by a function SET option +create or replace function myfunc(int) returns text as $$ +begin + set local work_mem = '2MB'; + return current_setting('work_mem'); +end $$ +language plpgsql +set work_mem = '1MB'; +select myfunc(0), current_setting('work_mem'); + myfunc | current_setting +--------+----------------- + 2MB | 3MB +(1 row) + +alter function myfunc(int) reset all; +select myfunc(0), current_setting('work_mem'); + myfunc | current_setting +--------+----------------- + 2MB | 2MB +(1 row) + +set work_mem = '3MB'; +-- but SET isn't +create or replace function myfunc(int) returns text as $$ +begin + set work_mem = '2MB'; + return current_setting('work_mem'); +end $$ +language plpgsql +set work_mem = '1MB'; +select myfunc(0), current_setting('work_mem'); + myfunc | current_setting +--------+----------------- + 2MB | 2MB +(1 row) + +set work_mem = '3MB'; +-- it should roll back on error, though +create or replace function myfunc(int) returns text as $$ +begin + set work_mem = '2MB'; + perform 1/$1; + return current_setting('work_mem'); +end $$ +language plpgsql +set work_mem = '1MB'; +select myfunc(0); +ERROR: division by zero +CONTEXT: SQL statement "SELECT 1/$1" +PL/pgSQL function myfunc(integer) line 4 at PERFORM +select current_setting('work_mem'); + current_setting +----------------- + 3MB +(1 row) + +select myfunc(1), current_setting('work_mem'); + myfunc | current_setting +--------+----------------- + 2MB | 2MB +(1 row) + +-- check current_setting()'s behavior with invalid setting name +select current_setting('nosuch.setting'); -- FAIL +ERROR: unrecognized configuration parameter "nosuch.setting" +select current_setting('nosuch.setting', false); -- FAIL +ERROR: unrecognized configuration parameter "nosuch.setting" +select current_setting('nosuch.setting', true) is null; + ?column? +---------- + t +(1 row) + +-- after this, all three cases should yield 'nada' +set nosuch.setting = 'nada'; +select current_setting('nosuch.setting'); + current_setting +----------------- + nada +(1 row) + +select current_setting('nosuch.setting', false); + current_setting +----------------- + nada +(1 row) + +select current_setting('nosuch.setting', true); + current_setting +----------------- + nada +(1 row) + +-- Normally, CREATE FUNCTION should complain about invalid values in +-- function SET options; but not if check_function_bodies is off, +-- because that creates ordering hazards for pg_dump +create function func_with_bad_set() returns int as $$ select 1 $$ +language sql +set default_text_search_config = no_such_config; +NOTICE: text search configuration "no_such_config" does not exist +ERROR: invalid value for parameter "default_text_search_config": "no_such_config" +set check_function_bodies = off; +create function func_with_bad_set() returns int as $$ select 1 $$ +language sql +set default_text_search_config = no_such_config; +NOTICE: text search configuration "no_such_config" does not exist +select func_with_bad_set(); +ERROR: invalid value for parameter "default_text_search_config": "no_such_config" +reset check_function_bodies; +set default_with_oids to f; +-- Should not allow to set it to true. +set default_with_oids to t; +ERROR: tables declared WITH OIDS are not supported +-- Test GUC categories and flag patterns +SELECT pg_settings_get_flags(NULL); + pg_settings_get_flags +----------------------- + +(1 row) + +SELECT pg_settings_get_flags('does_not_exist'); + pg_settings_get_flags +----------------------- + +(1 row) + +CREATE TABLE tab_settings_flags AS SELECT name, category, + 'EXPLAIN' = ANY(flags) AS explain, + 'NO_RESET_ALL' = ANY(flags) AS no_reset_all, + 'NOT_IN_SAMPLE' = ANY(flags) AS not_in_sample, + 'RUNTIME_COMPUTED' = ANY(flags) AS runtime_computed + FROM pg_show_all_settings() AS psas, + pg_settings_get_flags(psas.name) AS flags; +-- Developer GUCs should be flagged with GUC_NOT_IN_SAMPLE: +SELECT name FROM tab_settings_flags + WHERE category = 'Developer Options' AND NOT not_in_sample + ORDER BY 1; + name +------ +(0 rows) + +-- Most query-tuning GUCs are flagged as valid for EXPLAIN. +-- default_statistics_target is an exception. +SELECT name FROM tab_settings_flags + WHERE category ~ '^Query Tuning' AND NOT explain + ORDER BY 1; + name +--------------------------- + default_statistics_target +(1 row) + +-- Runtime-computed GUCs should be part of the preset category. +SELECT name FROM tab_settings_flags + WHERE NOT category = 'Preset Options' AND runtime_computed + ORDER BY 1; + name +------ +(0 rows) + +-- Preset GUCs are flagged as NOT_IN_SAMPLE. +SELECT name FROM tab_settings_flags + WHERE category = 'Preset Options' AND NOT not_in_sample + ORDER BY 1; + name +------ +(0 rows) + +DROP TABLE tab_settings_flags; |