'prepare' is not quite schema-safe - Mailing list pgsql-general

From Vlad
Subject 'prepare' is not quite schema-safe
Date
Msg-id cd70c68105050118256f86e165@mail.gmail.com
Whole thread Raw
Responses Re: 'prepare' is not quite schema-safe
Re: [Dbdpg-general] 'prepare' is not quite schema-safe
List pgsql-general
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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: libpq and PQexecPrepared
Next
From: Typing80wpm@aol.com
Date:
Subject: I receieved an example of Rekall script