src/test/regress/expected/guc.out

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