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

Toby Corkindale <toby(dot)corkindale(at)strategicdata(dot)com(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
e: jerry(dot)sievers(at)comcast(dot)net
p: 732.216.7255