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 DateStyle 5--------------- 6 Postgres, MDY 7(1 row) 8 9-- SET to some nondefault value 10SET vacuum_cost_delay TO 40; 11SET datestyle = 'ISO, YMD'; 12SHOW vacuum_cost_delay; 13 vacuum_cost_delay 14------------------- 15 40ms 16(1 row) 17 18SHOW datestyle; 19 DateStyle 20----------- 21 ISO, YMD 22(1 row) 23 24SELECT '2006-08-13 12:34:56'::timestamptz; 25 timestamptz 26------------------------ 27 2006-08-13 12:34:56-07 28(1 row) 29 30-- SET LOCAL has no effect outside of a transaction 31SET LOCAL vacuum_cost_delay TO 50; 32WARNING: SET LOCAL can only be used in transaction blocks 33SHOW vacuum_cost_delay; 34 vacuum_cost_delay 35------------------- 36 40ms 37(1 row) 38 39SET LOCAL datestyle = 'SQL'; 40WARNING: SET LOCAL can only be used in transaction blocks 41SHOW datestyle; 42 DateStyle 43----------- 44 ISO, YMD 45(1 row) 46 47SELECT '2006-08-13 12:34:56'::timestamptz; 48 timestamptz 49------------------------ 50 2006-08-13 12:34:56-07 51(1 row) 52 53-- SET LOCAL within a transaction that commits 54BEGIN; 55SET LOCAL vacuum_cost_delay TO 50; 56SHOW vacuum_cost_delay; 57 vacuum_cost_delay 58------------------- 59 50ms 60(1 row) 61 62SET LOCAL datestyle = 'SQL'; 63SHOW datestyle; 64 DateStyle 65----------- 66 SQL, YMD 67(1 row) 68 69SELECT '2006-08-13 12:34:56'::timestamptz; 70 timestamptz 71------------------------- 72 08/13/2006 12:34:56 PDT 73(1 row) 74 75COMMIT; 76SHOW vacuum_cost_delay; 77 vacuum_cost_delay 78------------------- 79 40ms 80(1 row) 81 82SHOW datestyle; 83 DateStyle 84----------- 85 ISO, YMD 86(1 row) 87 88SELECT '2006-08-13 12:34:56'::timestamptz; 89 timestamptz 90------------------------ 91 2006-08-13 12:34:56-07 92(1 row) 93 94-- SET should be reverted after ROLLBACK 95BEGIN; 96SET vacuum_cost_delay TO 60; 97SHOW vacuum_cost_delay; 98 vacuum_cost_delay 99------------------- 100 60ms 101(1 row) 102 103SET datestyle = 'German'; 104SHOW datestyle; 105 DateStyle 106------------- 107 German, DMY 108(1 row) 109 110SELECT '2006-08-13 12:34:56'::timestamptz; 111 timestamptz 112------------------------- 113 13.08.2006 12:34:56 PDT 114(1 row) 115 116ROLLBACK; 117SHOW vacuum_cost_delay; 118 vacuum_cost_delay 119------------------- 120 40ms 121(1 row) 122 123SHOW datestyle; 124 DateStyle 125----------- 126 ISO, YMD 127(1 row) 128 129SELECT '2006-08-13 12:34:56'::timestamptz; 130 timestamptz 131------------------------ 132 2006-08-13 12:34:56-07 133(1 row) 134 135-- Some tests with subtransactions 136BEGIN; 137SET vacuum_cost_delay TO 70; 138SET datestyle = 'MDY'; 139SHOW datestyle; 140 DateStyle 141----------- 142 ISO, MDY 143(1 row) 144 145SELECT '2006-08-13 12:34:56'::timestamptz; 146 timestamptz 147------------------------ 148 2006-08-13 12:34:56-07 149(1 row) 150 151SAVEPOINT first_sp; 152SET vacuum_cost_delay TO 80; 153SHOW vacuum_cost_delay; 154 vacuum_cost_delay 155------------------- 156 80ms 157(1 row) 158 159SET datestyle = 'German, DMY'; 160SHOW datestyle; 161 DateStyle 162------------- 163 German, DMY 164(1 row) 165 166SELECT '2006-08-13 12:34:56'::timestamptz; 167 timestamptz 168------------------------- 169 13.08.2006 12:34:56 PDT 170(1 row) 171 172ROLLBACK TO first_sp; 173SHOW datestyle; 174 DateStyle 175----------- 176 ISO, MDY 177(1 row) 178 179SELECT '2006-08-13 12:34:56'::timestamptz; 180 timestamptz 181------------------------ 182 2006-08-13 12:34:56-07 183(1 row) 184 185SAVEPOINT second_sp; 186SET vacuum_cost_delay TO 90; 187SET datestyle = 'SQL, YMD'; 188SHOW datestyle; 189 DateStyle 190----------- 191 SQL, YMD 192(1 row) 193 194SELECT '2006-08-13 12:34:56'::timestamptz; 195 timestamptz 196------------------------- 197 08/13/2006 12:34:56 PDT 198(1 row) 199 200SAVEPOINT third_sp; 201SET vacuum_cost_delay TO 100; 202SHOW vacuum_cost_delay; 203 vacuum_cost_delay 204------------------- 205 100ms 206(1 row) 207 208SET datestyle = 'Postgres, MDY'; 209SHOW datestyle; 210 DateStyle 211--------------- 212 Postgres, MDY 213(1 row) 214 215SELECT '2006-08-13 12:34:56'::timestamptz; 216 timestamptz 217------------------------------ 218 Sun Aug 13 12:34:56 2006 PDT 219(1 row) 220 221ROLLBACK TO third_sp; 222SHOW vacuum_cost_delay; 223 vacuum_cost_delay 224------------------- 225 90ms 226(1 row) 227 228SHOW datestyle; 229 DateStyle 230----------- 231 SQL, YMD 232(1 row) 233 234SELECT '2006-08-13 12:34:56'::timestamptz; 235 timestamptz 236------------------------- 237 08/13/2006 12:34:56 PDT 238(1 row) 239 240ROLLBACK TO second_sp; 241SHOW vacuum_cost_delay; 242 vacuum_cost_delay 243------------------- 244 70ms 245(1 row) 246 247SHOW datestyle; 248 DateStyle 249----------- 250 ISO, MDY 251(1 row) 252 253SELECT '2006-08-13 12:34:56'::timestamptz; 254 timestamptz 255------------------------ 256 2006-08-13 12:34:56-07 257(1 row) 258 259ROLLBACK; 260SHOW vacuum_cost_delay; 261 vacuum_cost_delay 262------------------- 263 40ms 264(1 row) 265 266SHOW datestyle; 267 DateStyle 268----------- 269 ISO, YMD 270(1 row) 271 272SELECT '2006-08-13 12:34:56'::timestamptz; 273 timestamptz 274------------------------ 275 2006-08-13 12:34:56-07 276(1 row) 277 278-- SET LOCAL with Savepoints 279BEGIN; 280SHOW vacuum_cost_delay; 281 vacuum_cost_delay 282------------------- 283 40ms 284(1 row) 285 286SHOW datestyle; 287 DateStyle 288----------- 289 ISO, YMD 290(1 row) 291 292SELECT '2006-08-13 12:34:56'::timestamptz; 293 timestamptz 294------------------------ 295 2006-08-13 12:34:56-07 296(1 row) 297 298SAVEPOINT sp; 299SET LOCAL vacuum_cost_delay TO 30; 300SHOW vacuum_cost_delay; 301 vacuum_cost_delay 302------------------- 303 30ms 304(1 row) 305 306SET LOCAL datestyle = 'Postgres, MDY'; 307SHOW datestyle; 308 DateStyle 309--------------- 310 Postgres, MDY 311(1 row) 312 313SELECT '2006-08-13 12:34:56'::timestamptz; 314 timestamptz 315------------------------------ 316 Sun Aug 13 12:34:56 2006 PDT 317(1 row) 318 319ROLLBACK TO sp; 320SHOW vacuum_cost_delay; 321 vacuum_cost_delay 322------------------- 323 40ms 324(1 row) 325 326SHOW datestyle; 327 DateStyle 328----------- 329 ISO, YMD 330(1 row) 331 332SELECT '2006-08-13 12:34:56'::timestamptz; 333 timestamptz 334------------------------ 335 2006-08-13 12:34:56-07 336(1 row) 337 338ROLLBACK; 339SHOW vacuum_cost_delay; 340 vacuum_cost_delay 341------------------- 342 40ms 343(1 row) 344 345SHOW datestyle; 346 DateStyle 347----------- 348 ISO, YMD 349(1 row) 350 351SELECT '2006-08-13 12:34:56'::timestamptz; 352 timestamptz 353------------------------ 354 2006-08-13 12:34:56-07 355(1 row) 356 357-- SET LOCAL persists through RELEASE (which was not true in 8.0-8.2) 358BEGIN; 359SHOW vacuum_cost_delay; 360 vacuum_cost_delay 361------------------- 362 40ms 363(1 row) 364 365SHOW datestyle; 366 DateStyle 367----------- 368 ISO, YMD 369(1 row) 370 371SELECT '2006-08-13 12:34:56'::timestamptz; 372 timestamptz 373------------------------ 374 2006-08-13 12:34:56-07 375(1 row) 376 377SAVEPOINT sp; 378SET LOCAL vacuum_cost_delay TO 30; 379SHOW vacuum_cost_delay; 380 vacuum_cost_delay 381------------------- 382 30ms 383(1 row) 384 385SET LOCAL datestyle = 'Postgres, MDY'; 386SHOW datestyle; 387 DateStyle 388--------------- 389 Postgres, MDY 390(1 row) 391 392SELECT '2006-08-13 12:34:56'::timestamptz; 393 timestamptz 394------------------------------ 395 Sun Aug 13 12:34:56 2006 PDT 396(1 row) 397 398RELEASE SAVEPOINT sp; 399SHOW vacuum_cost_delay; 400 vacuum_cost_delay 401------------------- 402 30ms 403(1 row) 404 405SHOW datestyle; 406 DateStyle 407--------------- 408 Postgres, MDY 409(1 row) 410 411SELECT '2006-08-13 12:34:56'::timestamptz; 412 timestamptz 413------------------------------ 414 Sun Aug 13 12:34:56 2006 PDT 415(1 row) 416 417ROLLBACK; 418SHOW vacuum_cost_delay; 419 vacuum_cost_delay 420------------------- 421 40ms 422(1 row) 423 424SHOW datestyle; 425 DateStyle 426----------- 427 ISO, YMD 428(1 row) 429 430SELECT '2006-08-13 12:34:56'::timestamptz; 431 timestamptz 432------------------------ 433 2006-08-13 12:34:56-07 434(1 row) 435 436-- SET followed by SET LOCAL 437BEGIN; 438SET vacuum_cost_delay TO 40; 439SET LOCAL vacuum_cost_delay TO 50; 440SHOW vacuum_cost_delay; 441 vacuum_cost_delay 442------------------- 443 50ms 444(1 row) 445 446SET datestyle = 'ISO, DMY'; 447SET LOCAL datestyle = 'Postgres, MDY'; 448SHOW datestyle; 449 DateStyle 450--------------- 451 Postgres, MDY 452(1 row) 453 454SELECT '2006-08-13 12:34:56'::timestamptz; 455 timestamptz 456------------------------------ 457 Sun Aug 13 12:34:56 2006 PDT 458(1 row) 459 460COMMIT; 461SHOW vacuum_cost_delay; 462 vacuum_cost_delay 463------------------- 464 40ms 465(1 row) 466 467SHOW datestyle; 468 DateStyle 469----------- 470 ISO, DMY 471(1 row) 472 473SELECT '2006-08-13 12:34:56'::timestamptz; 474 timestamptz 475------------------------ 476 2006-08-13 12:34:56-07 477(1 row) 478 479-- 480-- Test RESET. We use datestyle because the reset value is forced by 481-- pg_regress, so it doesn't depend on the installation's configuration. 482-- 483SET datestyle = iso, ymd; 484SHOW datestyle; 485 DateStyle 486----------- 487 ISO, YMD 488(1 row) 489 490SELECT '2006-08-13 12:34:56'::timestamptz; 491 timestamptz 492------------------------ 493 2006-08-13 12:34:56-07 494(1 row) 495 496RESET datestyle; 497SHOW datestyle; 498 DateStyle 499--------------- 500 Postgres, MDY 501(1 row) 502 503SELECT '2006-08-13 12:34:56'::timestamptz; 504 timestamptz 505------------------------------ 506 Sun Aug 13 12:34:56 2006 PDT 507(1 row) 508 509-- Test some simple error cases 510SET seq_page_cost TO 'NaN'; 511ERROR: parameter "seq_page_cost" requires a numeric value 512SET vacuum_cost_delay TO '10s'; 513ERROR: 10000 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100) 514-- 515-- Test DISCARD TEMP 516-- 517CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS; 518SELECT relname FROM pg_class WHERE relname = 'reset_test'; 519 relname 520------------ 521 reset_test 522(1 row) 523 524DISCARD TEMP; 525SELECT relname FROM pg_class WHERE relname = 'reset_test'; 526 relname 527--------- 528(0 rows) 529 530-- 531-- Test DISCARD ALL 532-- 533-- do changes 534DECLARE foo CURSOR WITH HOLD FOR SELECT 1; 535PREPARE foo AS SELECT 1; 536LISTEN foo_event; 537SET vacuum_cost_delay = 13; 538CREATE TEMP TABLE tmp_foo (data text) ON COMMIT DELETE ROWS; 539CREATE ROLE regress_guc_user; 540SET SESSION AUTHORIZATION regress_guc_user; 541-- look changes 542SELECT pg_listening_channels(); 543 pg_listening_channels 544----------------------- 545 foo_event 546(1 row) 547 548SELECT name FROM pg_prepared_statements; 549 name 550------ 551 foo 552(1 row) 553 554SELECT name FROM pg_cursors; 555 name 556------ 557 foo 558(1 row) 559 560SHOW vacuum_cost_delay; 561 vacuum_cost_delay 562------------------- 563 13ms 564(1 row) 565 566SELECT relname from pg_class where relname = 'tmp_foo'; 567 relname 568--------- 569 tmp_foo 570(1 row) 571 572SELECT current_user = 'regress_guc_user'; 573 ?column? 574---------- 575 t 576(1 row) 577 578-- discard everything 579DISCARD ALL; 580-- look again 581SELECT pg_listening_channels(); 582 pg_listening_channels 583----------------------- 584(0 rows) 585 586SELECT name FROM pg_prepared_statements; 587 name 588------ 589(0 rows) 590 591SELECT name FROM pg_cursors; 592 name 593------ 594(0 rows) 595 596SHOW vacuum_cost_delay; 597 vacuum_cost_delay 598------------------- 599 0 600(1 row) 601 602SELECT relname from pg_class where relname = 'tmp_foo'; 603 relname 604--------- 605(0 rows) 606 607SELECT current_user = 'regress_guc_user'; 608 ?column? 609---------- 610 f 611(1 row) 612 613DROP ROLE regress_guc_user; 614-- 615-- search_path should react to changes in pg_namespace 616-- 617set search_path = foo, public, not_there_initially; 618select current_schemas(false); 619 current_schemas 620----------------- 621 {public} 622(1 row) 623 624create schema not_there_initially; 625select current_schemas(false); 626 current_schemas 627------------------------------ 628 {public,not_there_initially} 629(1 row) 630 631drop schema not_there_initially; 632select current_schemas(false); 633 current_schemas 634----------------- 635 {public} 636(1 row) 637 638reset search_path; 639-- 640-- Tests for function-local GUC settings 641-- 642set work_mem = '3MB'; 643create function report_guc(text) returns text as 644$$ select current_setting($1) $$ language sql 645set work_mem = '1MB'; 646select report_guc('work_mem'), current_setting('work_mem'); 647 report_guc | current_setting 648------------+----------------- 649 1MB | 3MB 650(1 row) 651 652alter function report_guc(text) set work_mem = '2MB'; 653select report_guc('work_mem'), current_setting('work_mem'); 654 report_guc | current_setting 655------------+----------------- 656 2MB | 3MB 657(1 row) 658 659alter function report_guc(text) reset all; 660select report_guc('work_mem'), current_setting('work_mem'); 661 report_guc | current_setting 662------------+----------------- 663 3MB | 3MB 664(1 row) 665 666-- SET LOCAL is restricted by a function SET option 667create or replace function myfunc(int) returns text as $$ 668begin 669 set local work_mem = '2MB'; 670 return current_setting('work_mem'); 671end $$ 672language plpgsql 673set work_mem = '1MB'; 674select myfunc(0), current_setting('work_mem'); 675 myfunc | current_setting 676--------+----------------- 677 2MB | 3MB 678(1 row) 679 680alter function myfunc(int) reset all; 681select myfunc(0), current_setting('work_mem'); 682 myfunc | current_setting 683--------+----------------- 684 2MB | 2MB 685(1 row) 686 687set work_mem = '3MB'; 688-- but SET isn't 689create or replace function myfunc(int) returns text as $$ 690begin 691 set work_mem = '2MB'; 692 return current_setting('work_mem'); 693end $$ 694language plpgsql 695set work_mem = '1MB'; 696select myfunc(0), current_setting('work_mem'); 697 myfunc | current_setting 698--------+----------------- 699 2MB | 2MB 700(1 row) 701 702set work_mem = '3MB'; 703-- it should roll back on error, though 704create or replace function myfunc(int) returns text as $$ 705begin 706 set work_mem = '2MB'; 707 perform 1/$1; 708 return current_setting('work_mem'); 709end $$ 710language plpgsql 711set work_mem = '1MB'; 712select myfunc(0); 713ERROR: division by zero 714CONTEXT: SQL statement "SELECT 1/$1" 715PL/pgSQL function myfunc(integer) line 4 at PERFORM 716select current_setting('work_mem'); 717 current_setting 718----------------- 719 3MB 720(1 row) 721 722select myfunc(1), current_setting('work_mem'); 723 myfunc | current_setting 724--------+----------------- 725 2MB | 2MB 726(1 row) 727 728-- check current_setting()'s behavior with invalid setting name 729select current_setting('nosuch.setting'); -- FAIL 730ERROR: unrecognized configuration parameter "nosuch.setting" 731select current_setting('nosuch.setting', false); -- FAIL 732ERROR: unrecognized configuration parameter "nosuch.setting" 733select current_setting('nosuch.setting', true) is null; 734 ?column? 735---------- 736 t 737(1 row) 738 739-- after this, all three cases should yield 'nada' 740set nosuch.setting = 'nada'; 741select current_setting('nosuch.setting'); 742 current_setting 743----------------- 744 nada 745(1 row) 746 747select current_setting('nosuch.setting', false); 748 current_setting 749----------------- 750 nada 751(1 row) 752 753select current_setting('nosuch.setting', true); 754 current_setting 755----------------- 756 nada 757(1 row) 758 759-- Normally, CREATE FUNCTION should complain about invalid values in 760-- function SET options; but not if check_function_bodies is off, 761-- because that creates ordering hazards for pg_dump 762create function func_with_bad_set() returns int as $$ select 1 $$ 763language sql 764set default_text_search_config = no_such_config; 765NOTICE: text search configuration "no_such_config" does not exist 766ERROR: invalid value for parameter "default_text_search_config": "no_such_config" 767set check_function_bodies = off; 768create function func_with_bad_set() returns int as $$ select 1 $$ 769language sql 770set default_text_search_config = no_such_config; 771NOTICE: text search configuration "no_such_config" does not exist 772select func_with_bad_set(); 773ERROR: invalid value for parameter "default_text_search_config": "no_such_config" 774reset check_function_bodies; 775