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
