1 files changed, 298 insertions, 0 deletions
diff --git a/src/test/regress/sql/guc.sql b/src/test/regress/sql/guc.sql
new file mode 100644
index 0000000..43dbba3
--- /dev/null
+++ b/src/test/regress/sql/guc.sql
@@ -0,0 +1,298 @@
+-- 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;
+
+-- SET to some nondefault value
+SET vacuum_cost_delay TO 40;
+SET datestyle = 'ISO, YMD';
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+-- SET LOCAL has no effect outside of a transaction
+SET LOCAL vacuum_cost_delay TO 50;
+SHOW vacuum_cost_delay;
+SET LOCAL datestyle = 'SQL';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+-- SET LOCAL within a transaction that commits
+BEGIN;
+SET LOCAL vacuum_cost_delay TO 50;
+SHOW vacuum_cost_delay;
+SET LOCAL datestyle = 'SQL';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+COMMIT;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+-- SET should be reverted after ROLLBACK
+BEGIN;
+SET vacuum_cost_delay TO 60;
+SHOW vacuum_cost_delay;
+SET datestyle = 'German';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+ROLLBACK;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+-- Some tests with subtransactions
+BEGIN;
+SET vacuum_cost_delay TO 70;
+SET datestyle = 'MDY';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+SAVEPOINT first_sp;
+SET vacuum_cost_delay TO 80.1;
+SHOW vacuum_cost_delay;
+SET datestyle = 'German, DMY';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+ROLLBACK TO first_sp;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+SAVEPOINT second_sp;
+SET vacuum_cost_delay TO '900us';
+SET datestyle = 'SQL, YMD';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+SAVEPOINT third_sp;
+SET vacuum_cost_delay TO 100;
+SHOW vacuum_cost_delay;
+SET datestyle = 'Postgres, MDY';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+ROLLBACK TO third_sp;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+ROLLBACK TO second_sp;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+ROLLBACK;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+-- SET LOCAL with Savepoints
+BEGIN;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+SAVEPOINT sp;
+SET LOCAL vacuum_cost_delay TO 30;
+SHOW vacuum_cost_delay;
+SET LOCAL datestyle = 'Postgres, MDY';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+ROLLBACK TO sp;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+ROLLBACK;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+-- SET LOCAL persists through RELEASE (which was not true in 8.0-8.2)
+BEGIN;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+SAVEPOINT sp;
+SET LOCAL vacuum_cost_delay TO 30;
+SHOW vacuum_cost_delay;
+SET LOCAL datestyle = 'Postgres, MDY';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+RELEASE SAVEPOINT sp;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+ROLLBACK;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+-- SET followed by SET LOCAL
+BEGIN;
+SET vacuum_cost_delay TO 40;
+SET LOCAL vacuum_cost_delay TO 50;
+SHOW vacuum_cost_delay;
+SET datestyle = 'ISO, DMY';
+SET LOCAL datestyle = 'Postgres, MDY';
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+COMMIT;
+SHOW vacuum_cost_delay;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+--
+-- 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;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+RESET datestyle;
+SHOW datestyle;
+SELECT '2006-08-13 12:34:56'::timestamptz;
+
+-- Test some simple error cases
+SET seq_page_cost TO 'NaN';
+SET vacuum_cost_delay TO '10s';
+
+--
+-- Test DISCARD TEMP
+--
+CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS;
+SELECT relname FROM pg_class WHERE relname = 'reset_test';
+DISCARD TEMP;
+SELECT relname FROM pg_class WHERE relname = 'reset_test';
+
+--
+-- 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();
+SELECT name FROM pg_prepared_statements;
+SELECT name FROM pg_cursors;
+SHOW vacuum_cost_delay;
+SELECT relname from pg_class where relname = 'tmp_foo';
+SELECT current_user = 'regress_guc_user';
+-- discard everything
+DISCARD ALL;
+-- look again
+SELECT pg_listening_channels();
+SELECT name FROM pg_prepared_statements;
+SELECT name FROM pg_cursors;
+SHOW vacuum_cost_delay;
+SELECT relname from pg_class where relname = 'tmp_foo';
+SELECT current_user = 'regress_guc_user';
+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);
+create schema not_there_initially;
+select current_schemas(false);
+drop schema not_there_initially;
+select current_schemas(false);
+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');
+
+alter function report_guc(text) set work_mem = '2MB';
+
+select report_guc('work_mem'), current_setting('work_mem');
+
+alter function report_guc(text) reset all;
+
+select report_guc('work_mem'), current_setting('work_mem');
+
+-- 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');
+
+alter function myfunc(int) reset all;
+
+select myfunc(0), current_setting('work_mem');
+
+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');
+
+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);
+select current_setting('work_mem');
+select myfunc(1), current_setting('work_mem');
+
+-- check current_setting()'s behavior with invalid setting name
+
+select current_setting('nosuch.setting');  -- FAIL
+select current_setting('nosuch.setting', false);  -- FAIL
+select current_setting('nosuch.setting', true) is null;
+
+-- after this, all three cases should yield 'nada'
+set nosuch.setting = 'nada';
+
+select current_setting('nosuch.setting');
+select current_setting('nosuch.setting', false);
+select current_setting('nosuch.setting', true);
+
+-- 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;
+
+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;
+
+select func_with_bad_set();
+
+reset check_function_bodies;
+
+set default_with_oids to f;
+-- Should not allow to set it to true.
+set default_with_oids to t;