Re: 'prepare' is not quite schema-safe

Tom,

thanks for you reply.

> That's what it is supposed to do. It would hardly be possible to
> "prepare" a query at all if we had to wait till EXECUTE to find out
> which tables it was supposed to use.

I understand that from postgresql point of view everything is logical.
>From the application that serves multiple (identical) queries using
the same DB connection and switching the schemas depends of the
account a query came for it turns into oddity with the switch from
DBD::Pg 1.32 (which caches prepared queries internally AFAIK) to
DBD::Pg 1.41 wich has postgresql prepare the query...

i.e. the following perl code won't work correctly with DBD::Pg 1.40+

$dbh->do("SET search_path TO one");
my $sth1 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?");
$sth1->execute("one");

$dbh->do("set search_path to two");
my $sth2 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?");
$sth2->execute("two");

in the last call $sth1 prepared query will be actually executed, i.e.
"one.test" table used, not "two.test" as a programmer would expect!

--
Vlad