Обсуждение: Inconsistent "Set Local search_path" behavior in JDBC

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.