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
9
-- SET to some nondefault value
10
SET vacuum_cost_delay TO 40;
11
SET datestyle = 'ISO, YMD';
12
SHOW vacuum_cost_delay;
24
SELECT '2006-08-13 12:34:56'::timestamptz;
26
------------------------
27
2006-08-13 12:34:56-07
30
-- SET LOCAL has no effect outside of a transaction
31
SET LOCAL vacuum_cost_delay TO 50;
32
SHOW vacuum_cost_delay;
38
SET LOCAL datestyle = 'SQL';
45
SELECT '2006-08-13 12:34:56'::timestamptz;
47
------------------------
48
2006-08-13 12:34:56-07
51
-- SET LOCAL within a transaction that commits
53
SET LOCAL vacuum_cost_delay TO 50;
54
SHOW vacuum_cost_delay;
60
SET LOCAL datestyle = 'SQL';
67
SELECT '2006-08-13 12:34:56'::timestamptz;
69
-------------------------
70
08/13/2006 12:34:56 PDT
74
SHOW vacuum_cost_delay;
86
SELECT '2006-08-13 12:34:56'::timestamptz;
88
------------------------
89
2006-08-13 12:34:56-07
92
-- SET should be reverted after ROLLBACK
94
SET vacuum_cost_delay TO 60;
95
SHOW vacuum_cost_delay;
101
SET datestyle = 'German';
108
SELECT '2006-08-13 12:34:56'::timestamptz;
110
-------------------------
111
13.08.2006 12:34:56 PDT
115
SHOW vacuum_cost_delay;
127
SELECT '2006-08-13 12:34:56'::timestamptz;
129
------------------------
130
2006-08-13 12:34:56-07
133
-- Some tests with subtransactions
135
SET vacuum_cost_delay TO 70;
136
SET datestyle = 'MDY';
143
SELECT '2006-08-13 12:34:56'::timestamptz;
145
------------------------
146
2006-08-13 12:34:56-07
150
SET vacuum_cost_delay TO 80;
151
SHOW vacuum_cost_delay;
157
SET datestyle = 'German, DMY';
164
SELECT '2006-08-13 12:34:56'::timestamptz;
166
-------------------------
167
13.08.2006 12:34:56 PDT
170
ROLLBACK TO first_sp;
177
SELECT '2006-08-13 12:34:56'::timestamptz;
179
------------------------
180
2006-08-13 12:34:56-07
184
SET vacuum_cost_delay TO 90;
185
SET datestyle = 'SQL, YMD';
192
SELECT '2006-08-13 12:34:56'::timestamptz;
194
-------------------------
195
08/13/2006 12:34:56 PDT
199
SET vacuum_cost_delay TO 100;
200
SHOW vacuum_cost_delay;
206
SET datestyle = 'Postgres, MDY';
213
SELECT '2006-08-13 12:34:56'::timestamptz;
215
------------------------------
216
Sun Aug 13 12:34:56 2006 PDT
219
ROLLBACK TO third_sp;
220
SHOW vacuum_cost_delay;
232
SELECT '2006-08-13 12:34:56'::timestamptz;
234
-------------------------
235
08/13/2006 12:34:56 PDT
238
ROLLBACK TO second_sp;
239
SHOW vacuum_cost_delay;
251
SELECT '2006-08-13 12:34:56'::timestamptz;
253
------------------------
254
2006-08-13 12:34:56-07
258
SHOW vacuum_cost_delay;
270
SELECT '2006-08-13 12:34:56'::timestamptz;
272
------------------------
273
2006-08-13 12:34:56-07
276
-- SET LOCAL with Savepoints
278
SHOW vacuum_cost_delay;
290
SELECT '2006-08-13 12:34:56'::timestamptz;
292
------------------------
293
2006-08-13 12:34:56-07
297
SET LOCAL vacuum_cost_delay TO 30;
298
SHOW vacuum_cost_delay;
304
SET LOCAL datestyle = 'Postgres, MDY';
311
SELECT '2006-08-13 12:34:56'::timestamptz;
313
------------------------------
314
Sun Aug 13 12:34:56 2006 PDT
318
SHOW vacuum_cost_delay;
330
SELECT '2006-08-13 12:34:56'::timestamptz;
332
------------------------
333
2006-08-13 12:34:56-07
337
SHOW vacuum_cost_delay;
349
SELECT '2006-08-13 12:34:56'::timestamptz;
351
------------------------
352
2006-08-13 12:34:56-07
355
-- SET LOCAL persists through RELEASE (which was not true in 8.0-8.2)
357
SHOW vacuum_cost_delay;
369
SELECT '2006-08-13 12:34:56'::timestamptz;
371
------------------------
372
2006-08-13 12:34:56-07
376
SET LOCAL vacuum_cost_delay TO 30;
377
SHOW vacuum_cost_delay;
383
SET LOCAL datestyle = 'Postgres, MDY';
390
SELECT '2006-08-13 12:34:56'::timestamptz;
392
------------------------------
393
Sun Aug 13 12:34:56 2006 PDT
396
RELEASE SAVEPOINT sp;
397
SHOW vacuum_cost_delay;
409
SELECT '2006-08-13 12:34:56'::timestamptz;
411
------------------------------
412
Sun Aug 13 12:34:56 2006 PDT
416
SHOW vacuum_cost_delay;
428
SELECT '2006-08-13 12:34:56'::timestamptz;
430
------------------------
431
2006-08-13 12:34:56-07
434
-- SET followed by SET LOCAL
436
SET vacuum_cost_delay TO 40;
437
SET LOCAL vacuum_cost_delay TO 50;
438
SHOW vacuum_cost_delay;
444
SET datestyle = 'ISO, DMY';
445
SET LOCAL datestyle = 'Postgres, MDY';
452
SELECT '2006-08-13 12:34:56'::timestamptz;
454
------------------------------
455
Sun Aug 13 12:34:56 2006 PDT
459
SHOW vacuum_cost_delay;
471
SELECT '2006-08-13 12:34:56'::timestamptz;
473
------------------------
474
2006-08-13 12:34:56-07
478
-- Test RESET. We use datestyle because the reset value is forced by
479
-- pg_regress, so it doesn't depend on the installation's configuration.
481
SET datestyle = iso, ymd;
488
SELECT '2006-08-13 12:34:56'::timestamptz;
490
------------------------
491
2006-08-13 12:34:56-07
501
SELECT '2006-08-13 12:34:56'::timestamptz;
503
------------------------------
504
Sun Aug 13 12:34:56 2006 PDT
510
CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS;
511
SELECT relname FROM pg_class WHERE relname = 'reset_test';
518
SELECT relname FROM pg_class WHERE relname = 'reset_test';
527
DECLARE foo CURSOR WITH HOLD FOR SELECT 1;
528
PREPARE foo AS SELECT 1;
530
SET vacuum_cost_delay = 13;
531
CREATE TEMP TABLE tmp_foo (data text) ON COMMIT DELETE ROWS;
532
CREATE ROLE temp_reset_user;
533
SET SESSION AUTHORIZATION temp_reset_user;
535
SELECT pg_listening_channels();
536
pg_listening_channels
537
-----------------------
541
SELECT name FROM pg_prepared_statements;
547
SELECT name FROM pg_cursors;
553
SHOW vacuum_cost_delay;
559
SELECT relname from pg_class where relname = 'tmp_foo';
565
SELECT current_user = 'temp_reset_user';
571
-- discard everything
574
SELECT pg_listening_channels();
575
pg_listening_channels
576
-----------------------
579
SELECT name FROM pg_prepared_statements;
584
SELECT name FROM pg_cursors;
589
SHOW vacuum_cost_delay;
595
SELECT relname from pg_class where relname = 'tmp_foo';
600
SELECT current_user = 'temp_reset_user';
606
DROP ROLE temp_reset_user;
608
-- search_path should react to changes in pg_namespace
610
set search_path = foo, public, not_there_initially;
611
select current_schemas(false);
617
create schema not_there_initially;
618
select current_schemas(false);
620
------------------------------
621
{public,not_there_initially}
624
drop schema not_there_initially;
625
select current_schemas(false);
633
-- Tests for function-local GUC settings
635
set work_mem = '3MB';
636
create function report_guc(text) returns text as
637
$$ select current_setting($1) $$ language sql
638
set work_mem = '1MB';
639
select report_guc('work_mem'), current_setting('work_mem');
640
report_guc | current_setting
641
------------+-----------------
645
alter function report_guc(text) set work_mem = '2MB';
646
select report_guc('work_mem'), current_setting('work_mem');
647
report_guc | current_setting
648
------------+-----------------
652
alter function report_guc(text) reset all;
653
select report_guc('work_mem'), current_setting('work_mem');
654
report_guc | current_setting
655
------------+-----------------
659
-- SET LOCAL is restricted by a function SET option
660
create or replace function myfunc(int) returns text as $$
662
set local work_mem = '2MB';
663
return current_setting('work_mem');
666
set work_mem = '1MB';
667
select myfunc(0), current_setting('work_mem');
668
myfunc | current_setting
669
--------+-----------------
673
alter function myfunc(int) reset all;
674
select myfunc(0), current_setting('work_mem');
675
myfunc | current_setting
676
--------+-----------------
680
set work_mem = '3MB';
682
create or replace function myfunc(int) returns text as $$
684
set work_mem = '2MB';
685
return current_setting('work_mem');
688
set work_mem = '1MB';
689
select myfunc(0), current_setting('work_mem');
690
myfunc | current_setting
691
--------+-----------------
695
set work_mem = '3MB';
696
-- it should roll back on error, though
697
create or replace function myfunc(int) returns text as $$
699
set work_mem = '2MB';
701
return current_setting('work_mem');
704
set work_mem = '1MB';
706
ERROR: division by zero
707
CONTEXT: SQL statement "SELECT 1/$1"
708
PL/pgSQL function myfunc(integer) line 4 at PERFORM
709
select current_setting('work_mem');
715
select myfunc(1), current_setting('work_mem');
716
myfunc | current_setting
717
--------+-----------------