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