Re: 'prepare' is not quite schema-safe

On Sun, May 01, 2005 at 11:19:16PM -0400, Tom Lane wrote:
> 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 ;-))

Isn't this behaving as documented? prepare_cached() is supposed to
return the original statement handle when you pass it the same string
a second time.

The docs for prepare_cached() are littered with "Don't do this unless
you understand the implications" warnings, as well as some kludges to
differentiate different cases.

Cheers,
Steve