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; 7 8SET datestyle = 'ISO, YMD'; 9 10SHOW vacuum_cost_delay; 11 12SHOW datestyle; 13 14SELECT 15 '2006-08-13 12:34:56'::timestamptz; 16 17-- SET LOCAL has no effect outside of a transaction 18SET LOCAL vacuum_cost_delay TO 50; 19 20SHOW vacuum_cost_delay; 21 22SET LOCAL datestyle = 'SQL'; 23 24SHOW datestyle; 25 26SELECT 27 '2006-08-13 12:34:56'::timestamptz; 28 29-- SET LOCAL within a transaction that commits 30BEGIN; 31SET LOCAL vacuum_cost_delay TO 50; 32SHOW vacuum_cost_delay; 33SET LOCAL datestyle = 'SQL'; 34SHOW datestyle; 35SELECT 36 '2006-08-13 12:34:56'::timestamptz; 37COMMIT; 38 39SHOW vacuum_cost_delay; 40 41SHOW datestyle; 42 43SELECT 44 '2006-08-13 12:34:56'::timestamptz; 45 46-- SET should be reverted after ROLLBACK 47BEGIN; 48SET vacuum_cost_delay TO 60; 49SHOW vacuum_cost_delay; 50SET datestyle = 'German'; 51SHOW datestyle; 52SELECT 53 '2006-08-13 12:34:56'::timestamptz; 54ROLLBACK; 55 56SHOW vacuum_cost_delay; 57 58SHOW datestyle; 59 60SELECT 61 '2006-08-13 12:34:56'::timestamptz; 62 63-- Some tests with subtransactions 64BEGIN; 65SET vacuum_cost_delay TO 70; 66SET datestyle = 'MDY'; 67SHOW datestyle; 68SELECT 69 '2006-08-13 12:34:56'::timestamptz; 70SAVEPOINT first_sp; 71SET vacuum_cost_delay TO 80.1; 72SHOW vacuum_cost_delay; 73SET datestyle = 'German, DMY'; 74SHOW datestyle; 75SELECT 76 '2006-08-13 12:34:56'::timestamptz; 77ROLLBACK TO first_sp; 78 79SHOW datestyle; 80 81SELECT 82 '2006-08-13 12:34:56'::timestamptz; 83 84SAVEPOINT second_sp; 85 86SET vacuum_cost_delay TO '900us'; 87 88SET datestyle = 'SQL, YMD'; 89 90SHOW datestyle; 91 92SELECT 93 '2006-08-13 12:34:56'::timestamptz; 94 95SAVEPOINT third_sp; 96 97SET vacuum_cost_delay TO 100; 98 99SHOW vacuum_cost_delay; 100 101SET datestyle = 'Postgres, MDY'; 102 103SHOW datestyle; 104 105SELECT 106 '2006-08-13 12:34:56'::timestamptz; 107 108ROLLBACK TO third_sp; 109 110SHOW vacuum_cost_delay; 111 112SHOW datestyle; 113 114SELECT 115 '2006-08-13 12:34:56'::timestamptz; 116 117ROLLBACK TO second_sp; 118 119SHOW vacuum_cost_delay; 120 121SHOW datestyle; 122 123SELECT 124 '2006-08-13 12:34:56'::timestamptz; 125 126ROLLBACK; 127 128SHOW vacuum_cost_delay; 129 130SHOW datestyle; 131 132SELECT 133 '2006-08-13 12:34:56'::timestamptz; 134 135-- SET LOCAL with Savepoints 136BEGIN; 137SHOW vacuum_cost_delay; 138SHOW datestyle; 139SELECT 140 '2006-08-13 12:34:56'::timestamptz; 141SAVEPOINT sp; 142SET LOCAL vacuum_cost_delay TO 30; 143SHOW vacuum_cost_delay; 144SET LOCAL datestyle = 'Postgres, MDY'; 145SHOW datestyle; 146SELECT 147 '2006-08-13 12:34:56'::timestamptz; 148ROLLBACK TO sp; 149 150SHOW vacuum_cost_delay; 151 152SHOW datestyle; 153 154SELECT 155 '2006-08-13 12:34:56'::timestamptz; 156 157ROLLBACK; 158 159SHOW vacuum_cost_delay; 160 161SHOW datestyle; 162 163SELECT 164 '2006-08-13 12:34:56'::timestamptz; 165 166-- SET LOCAL persists through RELEASE (which was not true in 8.0-8.2) 167BEGIN; 168SHOW vacuum_cost_delay; 169SHOW datestyle; 170SELECT 171 '2006-08-13 12:34:56'::timestamptz; 172SAVEPOINT sp; 173SET LOCAL vacuum_cost_delay TO 30; 174SHOW vacuum_cost_delay; 175SET LOCAL datestyle = 'Postgres, MDY'; 176SHOW datestyle; 177SELECT 178 '2006-08-13 12:34:56'::timestamptz; 179RELEASE SAVEPOINT sp; 180SHOW vacuum_cost_delay; 181SHOW datestyle; 182SELECT 183 '2006-08-13 12:34:56'::timestamptz; 184ROLLBACK; 185 186SHOW vacuum_cost_delay; 187 188SHOW datestyle; 189 190SELECT 191 '2006-08-13 12:34:56'::timestamptz; 192 193-- SET followed by SET LOCAL 194BEGIN; 195SET vacuum_cost_delay TO 40; 196SET LOCAL vacuum_cost_delay TO 50; 197SHOW vacuum_cost_delay; 198SET datestyle = 'ISO, DMY'; 199SET LOCAL datestyle = 'Postgres, MDY'; 200SHOW datestyle; 201SELECT 202 '2006-08-13 12:34:56'::timestamptz; 203COMMIT; 204 205SHOW vacuum_cost_delay; 206 207SHOW datestyle; 208 209SELECT 210 '2006-08-13 12:34:56'::timestamptz; 211 212-- 213-- Test RESET. We use datestyle because the reset value is forced by 214-- pg_regress, so it doesn't depend on the installation's configuration. 215-- 216SET datestyle = iso, ymd; 217 218SHOW datestyle; 219 220SELECT 221 '2006-08-13 12:34:56'::timestamptz; 222 223RESET datestyle; 224 225SHOW datestyle; 226 227SELECT 228 '2006-08-13 12:34:56'::timestamptz; 229 230-- Test some simple error cases 231SET seq_page_cost TO 'NaN'; 232 233SET vacuum_cost_delay TO '10s'; 234 235-- 236-- Test DISCARD TEMP 237-- 238CREATE TEMP TABLE reset_test ( 239 data text 240) ON COMMIT DELETE ROWS; 241 242SELECT 243 relname 244FROM 245 pg_class 246WHERE 247 relname = 'reset_test'; 248 249DISCARD TEMP; 250 251SELECT 252 relname 253FROM 254 pg_class 255WHERE 256 relname = 'reset_test'; 257 258-- 259-- Test DISCARD ALL 260-- 261-- do changes 262DECLARE foo CURSOR WITH HOLD FOR 263 SELECT 264 1; 265 266PREPARE foo AS 267SELECT 268 1; 269 270LISTEN foo_event; 271 272SET vacuum_cost_delay = 13; 273 274CREATE TEMP TABLE tmp_foo ( 275 data text 276) ON COMMIT DELETE ROWS; 277 278CREATE ROLE regress_guc_user; 279 280SET SESSION AUTHORIZATION regress_guc_user; 281 282-- look changes 283SELECT 284 pg_listening_channels(); 285 286SELECT 287 name 288FROM 289 pg_prepared_statements; 290 291SELECT 292 name 293FROM 294 pg_cursors; 295 296SHOW vacuum_cost_delay; 297 298SELECT 299 relname 300FROM 301 pg_class 302WHERE 303 relname = 'tmp_foo'; 304 305SELECT 306 CURRENT_USER = 'regress_guc_user'; 307 308-- discard everything 309DISCARD ALL; 310 311-- look again 312SELECT 313 pg_listening_channels(); 314 315SELECT 316 name 317FROM 318 pg_prepared_statements; 319 320SELECT 321 name 322FROM 323 pg_cursors; 324 325SHOW vacuum_cost_delay; 326 327SELECT 328 relname 329FROM 330 pg_class 331WHERE 332 relname = 'tmp_foo'; 333 334SELECT 335 CURRENT_USER = 'regress_guc_user'; 336 337DROP ROLE regress_guc_user; 338 339-- 340-- search_path should react to changes in pg_namespace 341-- 342SET search_path = foo, public, not_there_initially; 343 344SELECT 345 current_schemas(FALSE); 346 347CREATE SCHEMA not_there_initially; 348 349SELECT 350 current_schemas(FALSE); 351 352DROP SCHEMA not_there_initially; 353 354SELECT 355 current_schemas(FALSE); 356 357RESET search_path; 358 359-- 360-- Tests for function-local GUC settings 361-- 362SET work_mem = '3MB'; 363 364CREATE FUNCTION report_guc (text) 365 RETURNS text 366 AS $$ 367 SELECT 368 current_setting($1) 369$$ 370LANGUAGE sql 371SET work_mem = '1MB'; 372 373SELECT 374 report_guc ('work_mem'), 375 current_setting('work_mem'); 376 377ALTER FUNCTION report_guc (text) SET work_mem = '2MB'; 378 379SELECT 380 report_guc ('work_mem'), 381 current_setting('work_mem'); 382 383ALTER FUNCTION report_guc (text) RESET ALL; 384 385SELECT 386 report_guc ('work_mem'), 387 current_setting('work_mem'); 388 389-- SET LOCAL is restricted by a function SET option 390CREATE OR REPLACE FUNCTION myfunc (int) 391 RETURNS text 392 AS $$ 393BEGIN 394 SET local work_mem = '2MB'; 395 RETURN current_setting('work_mem'); 396END 397$$ 398LANGUAGE plpgsql 399SET work_mem = '1MB'; 400 401SELECT 402 myfunc (0), 403 current_setting('work_mem'); 404 405ALTER FUNCTION myfunc (int) RESET ALL; 406 407SELECT 408 myfunc (0), 409 current_setting('work_mem'); 410 411SET work_mem = '3MB'; 412 413-- but SET isn't 414CREATE OR REPLACE FUNCTION myfunc (int) 415 RETURNS text 416 AS $$ 417BEGIN 418 SET work_mem = '2MB'; 419 RETURN current_setting('work_mem'); 420END 421$$ 422LANGUAGE plpgsql 423SET work_mem = '1MB'; 424 425SELECT 426 myfunc (0), 427 current_setting('work_mem'); 428 429SET work_mem = '3MB'; 430 431-- it should roll back on error, though 432CREATE OR REPLACE FUNCTION myfunc (int) 433 RETURNS text 434 AS $$ 435BEGIN 436 SET work_mem = '2MB'; 437 PERFORM 438 1 / $1; 439 RETURN current_setting('work_mem'); 440END 441$$ 442LANGUAGE plpgsql 443SET work_mem = '1MB'; 444 445SELECT 446 myfunc (0); 447 448SELECT 449 current_setting('work_mem'); 450 451SELECT 452 myfunc (1), 453 current_setting('work_mem'); 454 455-- check current_setting()'s behavior with invalid setting name 456SELECT 457 current_setting('nosuch.setting'); 458 459-- FAIL 460SELECT 461 current_setting('nosuch.setting', FALSE); 462 463-- FAIL 464SELECT 465 current_setting('nosuch.setting', TRUE) IS NULL; 466 467-- after this, all three cases should yield 'nada' 468SET nosuch.setting = 'nada'; 469 470SELECT 471 current_setting('nosuch.setting'); 472 473SELECT 474 current_setting('nosuch.setting', FALSE); 475 476SELECT 477 current_setting('nosuch.setting', TRUE); 478 479-- Normally, CREATE FUNCTION should complain about invalid values in 480-- function SET options; but not if check_function_bodies is off, 481-- because that creates ordering hazards for pg_dump 482CREATE FUNCTION func_with_bad_set () 483 RETURNS int 484 AS $$ 485 SELECT 486 1 487$$ 488LANGUAGE sql 489SET default_text_search_config = no_such_config; 490 491SET check_function_bodies = OFF; 492 493CREATE FUNCTION func_with_bad_set () 494 RETURNS int 495 AS $$ 496 SELECT 497 1 498$$ 499LANGUAGE sql 500SET default_text_search_config = no_such_config; 501 502SELECT 503 func_with_bad_set (); 504 505RESET check_function_bodies; 506 507SET default_with_oids TO f; 508 509-- Should not allow to set it to true. 510SET default_with_oids TO t; 511 512