Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 815 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..59da91f
--- /dev/null
+++ b/src/test/regress/expected/guc.out
@@ -0,0 +1,815 @@
+-- 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"
+--
+-- 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