From: | Toby Corkindale <toby(dot)corkindale(at)strategicdata(dot)com(dot)au> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Bug? Prepared queries continue to use search_path from their preparation time |
Date: | 2012-07-11 07:43:37 |
Message-ID: | 4FFD2EA9.5030108@strategicdata.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Mamin | 2012-07-11 10:41:26 | foreign keys with constant part |
Previous Message | Berend Tober | 2012-07-11 05:01:59 | Re: Sequence moves forward when failover is triggerred |