Re: 'prepare' is not quite schema-safe

Vlad <marchenko(at)gmail(dot)com> writes:
> 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!

Hmm. The above is arguably a DBD::Pg bug: it should not expect that
it's okay to use the same prepared statement in both cases. I do not
know what the spec is for "prepare_cached", but it sure seems that the
concept is fraught with danger --- the client-side driver has very
little hope of knowing what server-side events might be reasons to
invalidate the query cache. (Not that the server side is presently
all that good about it, but at least the server side is fixable
in principle ;-))

regards, tom lane