Zdenek Kotala sent in regression tests for SET a few days ago which got turned down. I think however that the idea has merit and that only his implementation was not useful. Attached is another regression test script that executes some SET / SET LOCAL within transactions and subtransactions.
Joachim
diff -uNr cvs/pgsql/src/test/regress/expected/guc_set.out cvs.build/pgsql/src/test/regress/expected/guc_set.out --- cvs/pgsql/src/test/regress/expected/guc_set.out 1970-01-01 01:00:00.000000000 +0100 +++ cvs.build/pgsql/src/test/regress/expected/guc_set.out 2006-07-28 15:00:09.000000000 +0200 @@ -0,0 +1,135 @@ +-- SET vacuum_cost_delay to some value +SET vacuum_cost_delay TO 400; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 400ms +(1 row) + +-- SET LOCAL has no effect outside of a transaction +SET LOCAL vacuum_cost_delay TO 500; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 400ms +(1 row) + +-- SET LOCAL within a transaction that commits +BEGIN; +SET LOCAL vacuum_cost_delay TO 500; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 500ms +(1 row) + +COMMIT; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 400ms +(1 row) + +-- SET should be reverted after ROLLBACK +BEGIN; +SET vacuum_cost_delay TO 600; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 600ms +(1 row) + +ROLLBACK; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 400ms +(1 row) + +-- Some tests with subtransactions +BEGIN; +SET vacuum_cost_delay TO 700; +SAVEPOINT first_sp; +SET vacuum_cost_delay TO 800; +ROLLBACK TO first_sp; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 700ms +(1 row) + +SAVEPOINT second_sp; +SET vacuum_cost_delay TO 900; +SAVEPOINT third_sp; +SET vacuum_cost_delay TO 1000; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 1s +(1 row) + +ROLLBACK TO third_sp; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 900ms +(1 row) + +ROLLBACK TO second_sp; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 700ms +(1 row) + +ROLLBACK; +-- SET LOCAL with Savepoints +BEGIN; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 400ms +(1 row) + +SAVEPOINT sp; +SET LOCAL vacuum_cost_delay TO 300; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 300ms +(1 row) + +ROLLBACK TO sp; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 400ms +(1 row) + +ROLLBACK; +-- SET followed by SET LOCAL (this is implemented by means of the "tentative +-- value" in the implementation) +BEGIN; +SET vacuum_cost_delay TO 400; +SET LOCAL vacuum_cost_delay TO 500; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 500ms +(1 row) + +COMMIT; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 400ms +(1 row) + +-- this depends on the default value of vacuum_cost_delay +RESET vacuum_cost_delay; +SHOW vacuum_cost_delay; + vacuum_cost_delay +------------------- + 0 +(1 row) + diff -uNr cvs/pgsql/src/test/regress/parallel_schedule cvs.build/pgsql/src/test/regress/parallel_schedule --- cvs/pgsql/src/test/regress/parallel_schedule 2006-03-24 08:11:21.000000000 +0100 +++ cvs.build/pgsql/src/test/regress/parallel_schedule 2006-07-28 14:52:12.000000000 +0200 @@ -65,6 +65,7 @@ test: privileges test: misc +test: guc_set # ---------- # The fifth group of parallel test diff -uNr cvs/pgsql/src/test/regress/serial_schedule cvs.build/pgsql/src/test/regress/serial_schedule --- cvs/pgsql/src/test/regress/serial_schedule 2006-01-23 23:52:37.000000000 +0100 +++ cvs.build/pgsql/src/test/regress/serial_schedule 2006-07-28 14:52:33.000000000 +0200 @@ -79,6 +79,7 @@ test: prepared_xacts test: privileges test: misc +test: guc_set test: select_views test: portals_p2 test: rules diff -uNr cvs/pgsql/src/test/regress/sql/guc_set.sql cvs.build/pgsql/src/test/regress/sql/guc_set.sql --- cvs/pgsql/src/test/regress/sql/guc_set.sql 1970-01-01 01:00:00.000000000 +0100 +++ cvs.build/pgsql/src/test/regress/sql/guc_set.sql 2006-07-28 14:07:44.000000000 +0200 @@ -0,0 +1,63 @@ +-- SET vacuum_cost_delay to some value +SET vacuum_cost_delay TO 400; +SHOW vacuum_cost_delay; + +-- SET LOCAL has no effect outside of a transaction +SET LOCAL vacuum_cost_delay TO 500; +SHOW vacuum_cost_delay; + +-- SET LOCAL within a transaction that commits +BEGIN; +SET LOCAL vacuum_cost_delay TO 500; +SHOW vacuum_cost_delay; +COMMIT; +SHOW vacuum_cost_delay; + +-- SET should be reverted after ROLLBACK +BEGIN; +SET vacuum_cost_delay TO 600; +SHOW vacuum_cost_delay; +ROLLBACK; +SHOW vacuum_cost_delay; + +-- Some tests with subtransactions +BEGIN; +SET vacuum_cost_delay TO 700; +SAVEPOINT first_sp; +SET vacuum_cost_delay TO 800; +ROLLBACK TO first_sp; +SHOW vacuum_cost_delay; +SAVEPOINT second_sp; +SET vacuum_cost_delay TO 900; +SAVEPOINT third_sp; +SET vacuum_cost_delay TO 1000; +SHOW vacuum_cost_delay; +ROLLBACK TO third_sp; +SHOW vacuum_cost_delay; +ROLLBACK TO second_sp; +SHOW vacuum_cost_delay; +ROLLBACK; + +-- SET LOCAL with Savepoints +BEGIN; +SHOW vacuum_cost_delay; +SAVEPOINT sp; +SET LOCAL vacuum_cost_delay TO 300; +SHOW vacuum_cost_delay; +ROLLBACK TO sp; +SHOW vacuum_cost_delay; +ROLLBACK; + +-- SET followed by SET LOCAL (this is implemented by means of the "tentative +-- value" in the implementation) +BEGIN; +SET vacuum_cost_delay TO 400; +SET LOCAL vacuum_cost_delay TO 500; +SHOW vacuum_cost_delay; +COMMIT; +SHOW vacuum_cost_delay; + +-- this depends on the default value of vacuum_cost_delay +RESET vacuum_cost_delay; +SHOW vacuum_cost_delay; +
---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
