Hello,
I'm seeking for an advise to solve the issue that we hit recently
(cost me sleepless night after production server upgrade).
The actual environment is Apache+mod_perl, Postgresql 8.0.2. After
upgrading DBD::Pg to the 1.41 version (which supports preparing quries
on "server" side) we hit a series of strange issues. After digging the
issue for two days I can provide the minimal example to illustrate
what happens:
CREATE SCHEMA one;
SET search_path TO one;
CREATE TABLE test ( item VARCHAR(20) );
INSERT INTO test VALUES( 'one' );
CREATE SCHEMA two;
SET search_path TO two;
CREATE TABLE test ( item VARCHAR(20) );
INSERT INTO test VALUES( 'two' );
SET search_path TO one;
PREPARE st( VARCHAR(20) ) AS SELECT * FROM test WHERE item = $1;
EXECUTE st( 'one' );
SET search_path TO two;
-- next statement fails because st selects from one.test, not from two.test
EXECUTE st( 'two' );
I'm not sure is it bug, feature or something else, but I'm looking for
some solution to either make "prepare" statement bound/apply to
"current schema unless specified in the statement" OR advise from the
list on how the above situation can be avoided. As a quick workaround
we reverted back to DBD::Pg 1.32 with no server-side prepare support.
p.s. I'm not cross posting both to pgsql-general and dbd-pg lists,
cause not sure from which side dbd::pg or postgrtesql a possible
workaround can be found from.
--
Vlad
