While doing some test development I observed the following behavior when using SET LOCAL search_path TO … within a adhoc (BEGIN … COMMIT) transaction.
I am working with a multiple schema database using 9.0.3 in Windows 7 64-bit / 9.0.801 driver
I had previously issued:
SET DATABASE search_path TO public, domain;
I then issue:
BEGIN;
SET LOCAL search_path = process, domain; [note that process is not on the database search path]
CREATE TABLE t …
COMMIT;
At which point the table appears in the process domain.
I then issue:
BEGIN;
SET LOCAL search_path = process, domain;
INSERT INTO t (…) VALUES (…);
COMMIT;
And I get an exception that:
“relation "t" does not exist”
If I then go back and issue:
SET DATABASE search_path TO public, domain, process; [adding process to the database search path]
I am able to successfully run the INSERT INTO transaction.
If I run the same commands (the INSERT INTO transaction) in my GUI environment (PostgreSQL Maestro) it does not matter whether the DATABASE search_path includes “process”. It, for some reason, also does not seem to matter when issuing the “CREATE TABLE” transaction within JDBC.
David J.