1-- pg_regress should ensure that this default value applies; however 2-- we can't rely on any specific default value of vacuum_cost_delay 3SHOW datestyle; 4 5-- SET to some nondefault value 6SET vacuum_cost_delay TO 40; 7SET datestyle = 'ISO, YMD'; 8SHOW vacuum_cost_delay; 9SHOW datestyle; 10SELECT '2006-08-13 12:34:56'::timestamptz; 11 12-- SET LOCAL has no effect outside of a transaction 13SET LOCAL vacuum_cost_delay TO 50; 14SHOW vacuum_cost_delay; 15SET LOCAL datestyle = 'SQL'; 16SHOW datestyle; 17SELECT '2006-08-13 12:34:56'::timestamptz; 18 19-- SET LOCAL within a transaction that commits 20BEGIN; 21SET LOCAL vacuum_cost_delay TO 50; 22SHOW vacuum_cost_delay; 23SET LOCAL datestyle = 'SQL'; 24SHOW datestyle; 25SELECT '2006-08-13 12:34:56'::timestamptz; 26COMMIT; 27SHOW vacuum_cost_delay; 28SHOW datestyle; 29SELECT '2006-08-13 12:34:56'::timestamptz; 30 31-- SET should be reverted after ROLLBACK 32BEGIN; 33SET vacuum_cost_delay TO 60; 34SHOW vacuum_cost_delay; 35SET datestyle = 'German'; 36SHOW datestyle; 37SELECT '2006-08-13 12:34:56'::timestamptz; 38ROLLBACK; 39SHOW vacuum_cost_delay; 40SHOW datestyle; 41SELECT '2006-08-13 12:34:56'::timestamptz; 42 43-- Some tests with subtransactions 44BEGIN; 45SET vacuum_cost_delay TO 70; 46SET datestyle = 'MDY'; 47SHOW datestyle; 48SELECT '2006-08-13 12:34:56'::timestamptz; 49SAVEPOINT first_sp; 50SET vacuum_cost_delay TO 80; 51SHOW vacuum_cost_delay; 52SET datestyle = 'German, DMY'; 53SHOW datestyle; 54SELECT '2006-08-13 12:34:56'::timestamptz; 55ROLLBACK TO first_sp; 56SHOW datestyle; 57SELECT '2006-08-13 12:34:56'::timestamptz; 58SAVEPOINT second_sp; 59SET vacuum_cost_delay TO 90; 60SET datestyle = 'SQL, YMD'; 61SHOW datestyle; 62SELECT '2006-08-13 12:34:56'::timestamptz; 63SAVEPOINT third_sp; 64SET vacuum_cost_delay TO 100; 65SHOW vacuum_cost_delay; 66SET datestyle = 'Postgres, MDY'; 67SHOW datestyle; 68SELECT '2006-08-13 12:34:56'::timestamptz; 69ROLLBACK TO third_sp; 70SHOW vacuum_cost_delay; 71SHOW datestyle; 72SELECT '2006-08-13 12:34:56'::timestamptz; 73ROLLBACK TO second_sp; 74SHOW vacuum_cost_delay; 75SHOW datestyle; 76SELECT '2006-08-13 12:34:56'::timestamptz; 77ROLLBACK; 78SHOW vacuum_cost_delay; 79SHOW datestyle; 80SELECT '2006-08-13 12:34:56'::timestamptz; 81 82-- SET LOCAL with Savepoints 83BEGIN; 84SHOW vacuum_cost_delay; 85SHOW datestyle; 86SELECT '2006-08-13 12:34:56'::timestamptz; 87SAVEPOINT sp; 88SET LOCAL vacuum_cost_delay TO 30; 89SHOW vacuum_cost_delay; 90SET LOCAL datestyle = 'Postgres, MDY'; 91SHOW datestyle; 92SELECT '2006-08-13 12:34:56'::timestamptz; 93ROLLBACK TO sp; 94SHOW vacuum_cost_delay; 95SHOW datestyle; 96SELECT '2006-08-13 12:34:56'::timestamptz; 97ROLLBACK; 98SHOW vacuum_cost_delay; 99SHOW datestyle; 100SELECT '2006-08-13 12:34:56'::timestamptz; 101 102-- SET LOCAL persists through RELEASE (which was not true in 8.0-8.2) 103BEGIN; 104SHOW vacuum_cost_delay; 105SHOW datestyle; 106SELECT '2006-08-13 12:34:56'::timestamptz; 107SAVEPOINT sp; 108SET LOCAL vacuum_cost_delay TO 30; 109SHOW vacuum_cost_delay; 110SET LOCAL datestyle = 'Postgres, MDY'; 111SHOW datestyle; 112SELECT '2006-08-13 12:34:56'::timestamptz; 113RELEASE SAVEPOINT sp; 114SHOW vacuum_cost_delay; 115SHOW datestyle; 116SELECT '2006-08-13 12:34:56'::timestamptz; 117ROLLBACK; 118SHOW vacuum_cost_delay; 119SHOW datestyle; 120SELECT '2006-08-13 12:34:56'::timestamptz; 121 122-- SET followed by SET LOCAL 123BEGIN; 124SET vacuum_cost_delay TO 40; 125SET LOCAL vacuum_cost_delay TO 50; 126SHOW vacuum_cost_delay; 127SET datestyle = 'ISO, DMY'; 128SET LOCAL datestyle = 'Postgres, MDY'; 129SHOW datestyle; 130SELECT '2006-08-13 12:34:56'::timestamptz; 131COMMIT; 132SHOW vacuum_cost_delay; 133SHOW datestyle; 134SELECT '2006-08-13 12:34:56'::timestamptz; 135 136-- 137-- Test RESET. We use datestyle because the reset value is forced by 138-- pg_regress, so it doesn't depend on the installation's configuration. 139-- 140SET datestyle = iso, ymd; 141SHOW datestyle; 142SELECT '2006-08-13 12:34:56'::timestamptz; 143RESET datestyle; 144SHOW datestyle; 145SELECT '2006-08-13 12:34:56'::timestamptz; 146 147-- Test some simple error cases 148SET seq_page_cost TO 'NaN'; 149SET vacuum_cost_delay TO '10s'; 150 151-- 152-- Test DISCARD TEMP 153-- 154CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS; 155SELECT relname FROM pg_class WHERE relname = 'reset_test'; 156DISCARD TEMP; 157SELECT relname FROM pg_class WHERE relname = 'reset_test'; 158 159-- 160-- Test DISCARD ALL 161-- 162 163-- do changes 164DECLARE foo CURSOR WITH HOLD FOR SELECT 1; 165PREPARE foo AS SELECT 1; 166LISTEN foo_event; 167SET vacuum_cost_delay = 13; 168CREATE TEMP TABLE tmp_foo (data text) ON COMMIT DELETE ROWS; 169CREATE ROLE regress_guc_user; 170SET SESSION AUTHORIZATION regress_guc_user; 171-- look changes 172SELECT pg_listening_channels(); 173SELECT name FROM pg_prepared_statements; 174SELECT name FROM pg_cursors; 175SHOW vacuum_cost_delay; 176SELECT relname from pg_class where relname = 'tmp_foo'; 177SELECT current_user = 'regress_guc_user'; 178-- discard everything 179DISCARD ALL; 180-- look again 181SELECT pg_listening_channels(); 182SELECT name FROM pg_prepared_statements; 183SELECT name FROM pg_cursors; 184SHOW vacuum_cost_delay; 185SELECT relname from pg_class where relname = 'tmp_foo'; 186SELECT current_user = 'regress_guc_user'; 187DROP ROLE regress_guc_user; 188 189-- 190-- search_path should react to changes in pg_namespace 191-- 192 193set search_path = foo, public, not_there_initially; 194select current_schemas(false); 195create schema not_there_initially; 196select current_schemas(false); 197drop schema not_there_initially; 198select current_schemas(false); 199reset search_path; 200 201-- 202-- Tests for function-local GUC settings 203-- 204 205set work_mem = '3MB'; 206 207create function report_guc(text) returns text as 208$$ select current_setting($1) $$ language sql 209set work_mem = '1MB'; 210 211select report_guc('work_mem'), current_setting('work_mem'); 212 213alter function report_guc(text) set work_mem = '2MB'; 214 215select report_guc('work_mem'), current_setting('work_mem'); 216 217alter function report_guc(text) reset all; 218 219select report_guc('work_mem'), current_setting('work_mem'); 220 221-- SET LOCAL is restricted by a function SET option 222create or replace function myfunc(int) returns text as $$ 223begin 224 set local work_mem = '2MB'; 225 return current_setting('work_mem'); 226end $$ 227language plpgsql 228set work_mem = '1MB'; 229 230select myfunc(0), current_setting('work_mem'); 231 232alter function myfunc(int) reset all; 233 234select myfunc(0), current_setting('work_mem'); 235 236set work_mem = '3MB'; 237 238-- but SET isn't 239create or replace function myfunc(int) returns text as $$ 240begin 241 set work_mem = '2MB'; 242 return current_setting('work_mem'); 243end $$ 244language plpgsql 245set work_mem = '1MB'; 246 247select myfunc(0), current_setting('work_mem'); 248 249set work_mem = '3MB'; 250 251-- it should roll back on error, though 252create or replace function myfunc(int) returns text as $$ 253begin 254 set work_mem = '2MB'; 255 perform 1/$1; 256 return current_setting('work_mem'); 257end $$ 258language plpgsql 259set work_mem = '1MB'; 260 261select myfunc(0); 262select current_setting('work_mem'); 263select myfunc(1), current_setting('work_mem'); 264 265-- check current_setting()'s behavior with invalid setting name 266 267select current_setting('nosuch.setting'); -- FAIL 268select current_setting('nosuch.setting', false); -- FAIL 269select current_setting('nosuch.setting', true) is null; 270 271-- after this, all three cases should yield 'nada' 272set nosuch.setting = 'nada'; 273 274select current_setting('nosuch.setting'); 275select current_setting('nosuch.setting', false); 276select current_setting('nosuch.setting', true); 277 278-- Normally, CREATE FUNCTION should complain about invalid values in 279-- function SET options; but not if check_function_bodies is off, 280-- because that creates ordering hazards for pg_dump 281 282create function func_with_bad_set() returns int as $$ select 1 $$ 283language sql 284set default_text_search_config = no_such_config; 285 286set check_function_bodies = off; 287 288create function func_with_bad_set() returns int as $$ select 1 $$ 289language sql 290set default_text_search_config = no_such_config; 291 292select func_with_bad_set(); 293 294reset check_function_bodies; 295