Thread: Bug? Prepared queries continue to use search_path from their preparation time

Hi,
I've encountered something that might be a bug in DBD::Pg, or might be a
feature of PostgreSQL itself.

The issue occurs when you have server-side prepared queries enabled, and
then change the search_path parameter after creating a prepared query.
Future executions of that query still seem to be using the original
search_path.

To replicate the issue, do the following:

$ createdb bug
$ psql bug
CREATE SCHEMA foo;
CREATE SCHEMA bar;
CREATE TABLE foo.example (id integer primary key);
CREATE TABLE bar.example (id integer primary key);
INSERT INTO foo.example (id) values (123);


Then run the following script:

#!/usr/bin/env perl
use 5.14.1;
use warnings;
use DBI;
# Requires DBD::Pg to be installed too

my $dbh = DBI->connect('dbi:Pg:dbname=bug', undef, undef,
     { pg_server_prepare => 1 }
);

$dbh->do("set search_path = foo,public");

my $q = $dbh->prepare("select count(*) from example where id = ?");

$q->execute(123);
my ($row) = $q->fetchrow_array;
say "First result: $row";

$dbh->do("set search_path = bar,public");
$q->execute(123);
($row) = $q->fetchrow_array;
say "First result: $row";



The output indicates that a row was found in both cases, however in the
second case, it should not have found anything because the search path
had changed.

-Toby

Toby Corkindale <toby.corkindale@strategicdata.com.au> writes:
> The issue occurs when you have server-side prepared queries enabled, and
> then change the search_path parameter after creating a prepared query.
> Future executions of that query still seem to be using the original
> search_path.

This behavior is intentional.

            regards, tom lane

Toby Corkindale <toby.corkindale@strategicdata.com.au> writes:

> Hi,
> I've encountered something that might be a bug in DBD::Pg, or might be
> a feature of PostgreSQL itself.
>
> The issue occurs when you have server-side prepared queries enabled,
> and then change the search_path parameter after creating a prepared
> query. Future executions of that query still seem to be using the
> original search_path.
>
> To replicate the issue, do the following:
>
> $ createdb bug
> $ psql bug
> CREATE SCHEMA foo;
> CREATE SCHEMA bar;
> CREATE TABLE foo.example (id integer primary key);
> CREATE TABLE bar.example (id integer primary key);
> INSERT INTO foo.example (id) values (123);
>
>
> Then run the following script:
>
> #!/usr/bin/env perl
> use 5.14.1;
> use warnings;
> use DBI;
> # Requires DBD::Pg to be installed too
>
> my $dbh = DBI->connect('dbi:Pg:dbname=bug', undef, undef,
>     { pg_server_prepare => 1 }
> );
>
> $dbh->do("set search_path = foo,public");
>
> my $q = $dbh->prepare("select count(*) from example where id = ?");
>
> $q->execute(123);
> my ($row) = $q->fetchrow_array;
> say "First result: $row";
>
> $dbh->do("set search_path = bar,public");
> $q->execute(123);
> ($row) = $q->fetchrow_array;
> say "First result: $row";
>
>
>
> The output indicates that a row was found in both cases, however in
> the second case, it should not have found anything because the search
> path had changed.

No, not a bug if you understand that prepared statements resolve
symbolic things like schema/object names into OIDs that are frozen by
the prepare.

You didn't mention what version you're on.

I did a similar test though using plain SQL prepared statements and
was surprised though to find that the DISCARD PLANS statement issued
after changing the search_path did nothing to change this behavior.

To wit;

create schema s1;
create schema s2;

create table s1.t (a int);
insert into s1.t values (1);

create table s2.t (a int);

set search_path to s1;

prepare foo as select a from t;

execute foo;

set search_path to s2;

discard plans;

execute foo;

drop schema s1 cascade;
drop schema s2 cascade;

----


sj$ psql --no-psqlrc -f s
CREATE SCHEMA
CREATE SCHEMA
CREATE TABLE
INSERT 0 1
CREATE TABLE
SET
PREPARE
 a
---
 1
(1 row)

SET
DISCARD PLANS
 a
---
 1      <---  was not expecting to see this here
(1 row)

DROP SCHEMA
DROP SCHEMA
sj$


> -Toby
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
e: jerry.sievers@comcast.net
p: 732.216.7255

Toby Corkindale <toby.corkindale@strategicdata.com.au> writes:

> Hi,
> I've encountered something that might be a bug in DBD::Pg, or might be
> a feature of PostgreSQL itself.
>
> The issue occurs when you have server-side prepared queries enabled,
> and then change the search_path parameter after creating a prepared
> query. Future executions of that query still seem to be using the
> original search_path.
>
> To replicate the issue, do the following:
>
> $ createdb bug
> $ psql bug
> CREATE SCHEMA foo;
> CREATE SCHEMA bar;
> CREATE TABLE foo.example (id integer primary key);
> CREATE TABLE bar.example (id integer primary key);
> INSERT INTO foo.example (id) values (123);
>
>
> Then run the following script:
>
> #!/usr/bin/env perl
> use 5.14.1;
> use warnings;
> use DBI;
> # Requires DBD::Pg to be installed too
>
> my $dbh = DBI->connect('dbi:Pg:dbname=bug', undef, undef,
>     { pg_server_prepare => 1 }
> );
>
> $dbh->do("set search_path = foo,public");
>
> my $q = $dbh->prepare("select count(*) from example where id = ?");
>
> $q->execute(123);
> my ($row) = $q->fetchrow_array;
> say "First result: $row";
>
> $dbh->do("set search_path = bar,public");
> $q->execute(123);
> ($row) = $q->fetchrow_array;
> say "First result: $row";
>
>
>
> The output indicates that a row was found in both cases, however in
> the second case, it should not have found anything because the search
> path had changed.

No, not a bug if you understand that prepared statements resolve
symbolic things like schema/object names into OIDs that are frozen by
the prepare.

You didn't mention what version you're on.

I did a similar test though using plain SQL prepared statements and
was surprised though to find that the DISCARD PLANS statement issued
after changing the search_path did nothing to change this behavior.

To wit;

create schema s1;
create schema s2;

create table s1.t (a int);
insert into s1.t values (1);

create table s2.t (a int);

set search_path to s1;

prepare foo as select a from t;

execute foo;

set search_path to s2;

discard plans;

execute foo;

drop schema s1 cascade;
drop schema s2 cascade;

----


sj$ psql --no-psqlrc -f s
CREATE SCHEMA
CREATE SCHEMA
CREATE TABLE
INSERT 0 1
CREATE TABLE
SET
PREPARE
 a
---
 1
(1 row)

SET
DISCARD PLANS
 a
---
 1      <---  was not expecting to see this here
(1 row)

DROP SCHEMA
DROP SCHEMA
sj$


> -Toby
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
e: jerry.sievers@comcast.net
p: 732.216.7255

On 12/07/12 00:07, Tom Lane wrote:
> Toby Corkindale <toby.corkindale@strategicdata.com.au> writes:
>> The issue occurs when you have server-side prepared queries enabled, and
>> then change the search_path parameter after creating a prepared query.
>> Future executions of that query still seem to be using the original
>> search_path.
>
> This behavior is intentional.

In that case, the error lies with the Perl libraries that automatically
prepare and cache queries, but do not include the search_path in the
cache keys.

I'll bring it up with them.

thanks for your advice,
Toby

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> This behavior is intentional.

> In that case, the error lies with the Perl libraries that automatically
> prepare and cache queries, but do not include the search_path in the
> cache keys.

You have lost me there. Which Perl libraries are those?

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201207121257
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk//AlQACgkQvJuQZxSWSsj6IACgzQJlK5Y1u1Pr3KDyR6ax3Elb
4ycAoOPo5XJn0Uxx7Lvg30u3iCBoibp6
=oKaV
-----END PGP SIGNATURE-----

On 13/07/12 02:59, Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>>> This behavior is intentional.
>
>> In that case, the error lies with the Perl libraries that automatically
>> prepare and cache queries, but do not include the search_path in the
>> cache keys.
>
> You have lost me there. Which Perl libraries are those?

I was thinking of DBIx::Class, which will automatically use the DBI
function prepare_cached() for all the queries it generates.

However I discovered this warning hidden deep in the documentation:

"[..] once your schema is instantiated, you should not change the
PostgreSQL schema search path for that schema's database connection. If
you do, Bad Things may happen."

So they are aware of the problem, but unfortunately I hadn't seen that
warning (and I suspect most users won't) until after they've encountered
the problem.

I guess for now I chalk this up to experience and remember not to do it
again! :)