Re: [Dbdpg-general] Re: 'prepare' is not quite schema-safe

Andrew Dunstan wrote:

>
>
> Vlad wrote:
>
>>
>> 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!
>>
>>
>
> Correctness seems to be in the eye of the beholder.
>
> It does what I as a programmer would expect. The behaviour you
> previously saw was an unfortunate byproduct of the fact that up to now
> DBD::Pg has emulated proper prepared statements, whereas now it uses
> them for real. Any application that relies on that broken byproduct is
> simply erroneous, IMNSHO.
>
> If you really need this, then as previously discussed on list, there
> is a way to turn off use of server-side prepared statements.
>
>

Oops. I missed that the code used prepare_cached() rather than just
prepare().

I am not sure this is reasonably fixable. Invalidating the cache is not
a pleasant solution - the query might not be affected by the change in
search path at all. I'd be inclined to say that this is just a
limitation of prepare_cached() which should be documented.

cheers

andrew