prepared statements and DBD::Pg

On May 6, 2009, at 9:39 AM, JP Fletcher wrote:

Hi,

I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1)
when the first command in a prepared statement is 'CREATE TEMP TABLE'.

For instance, this works:

my $prepare_sql =<<SQL;
CREATE TEMP TABLE foo( id int, user_id int,);

INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL

my $sth = $dbh->prepare($prepare_sql);

This produces the error

ERROR: cannot insert multiple commands into a prepared statement

my $prepare_sql =<<SQL;
INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL

my $sth = $dbh->prepare($prepare_sql);

Is this the expected behaviour?

You should follow up on the DBD::Pg list, but I would guess that the
module is doing a surface check to determine whether the statement is
a candidate for being prepared. I bet that whenever the first
statement is not a select, insert, delete, or update that you will not
get an error. What is happening ion these cases is that no preparation
is happening at all.

HTH,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

2009/5/7 JP Fletcher <jpfletch@ca.afilias.info>:

Hi,

I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the
first command in a prepared statement is 'CREATE TEMP TABLE'.

For instance, this works:

my $prepare_sql =<<SQL;
CREATE TEMP TABLE foo( id int, user_id int,);

INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL

my $sth = $dbh->prepare($prepare_sql);

This produces the error

ERROR: cannot insert multiple commands into a prepared statement

Blessed be CPAN and the manuals for DBD
http://search.cpan.org/~turnstep/DBD-Pg-2.13.1/Pg.pm#prepare

WARNING: DBD::Pg now (as of version 1.40) uses true prepared
statements by sending them to the backend to be prepared by the
Postgres server. Statements that were legal before may no longer work.
See below for details.

The prepare method prepares a statement for later execution.
PostgreSQL supports prepared statements, which enables DBD::Pg to only
send the query once, and simply send the arguments for every
subsequent call to "execute". DBD::Pg can use these server-side
prepared statements, or it can just send the entire query to the
server each time. The best way is automatically chosen for each query.
This will be sufficient for most users: keep reading for a more
detailed explanation and some optional flags.

Queries that do not begin with the word "SELECT", "INSERT", "UPDATE",
or "DELETE" are never sent as server-side prepared statements.

Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:

2009/5/7 JP Fletcher <jpfletch@ca.afilias.info>:

Hi,

I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the
first command in a prepared statement is 'CREATE TEMP TABLE'.

For instance, this works:

my $prepare_sql =<<SQL;
CREATE TEMP TABLE foo( id int, user_id int,);
INSERT INTO foo(1, 1);
INSERT INTO foo(2, 2);
SQL
my $sth = $dbh->prepare($prepare_sql);

This produces the error
ERROR: cannot insert multiple commands into a prepared statement

Blessed be CPAN and the manuals for DBD
http://search.cpan.org/~turnstep/DBD-Pg-2.13.1/Pg.pm#prepare

WARNING: DBD::Pg now (as of version 1.40) uses true prepared
statements by sending them to the backend to be prepared by the
Postgres server. Statements that were legal before may no longer work.

Sure seems like a bug, or at best a misfeature, that DBD::Pg doesn't
simply fallback to client-side prepare when a server-side prepare can't
be performed. I believe DBD::mysql does that.

Tim.

On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:

2009/5/7 JP Fletcher <jpfletch@ca.afilias.info>:

Hi,

I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the
first command in a prepared statement is 'CREATE TEMP TABLE'.

For instance, this works:

my $prepare_sql =<<SQL;
CREATE TEMP TABLE foo( id int, user_id int,);
INSERT INTO foo(1, 1);
INSERT INTO foo(2, 2);
SQL
my $sth = $dbh->prepare($prepare_sql);

This produces the error
ERROR: cannot insert multiple commands into a prepared statement

Blessed be CPAN and the manuals for DBD
http://search.cpan.org/~turnstep/DBD-Pg-2.13.1/Pg.pm#prepare

WARNING: DBD::Pg now (as of version 1.40) uses true prepared
statements by sending them to the backend to be prepared by the
Postgres server. Statements that were legal before may no longer
work.

Sure seems like a bug, or at best a misfeature, that DBD::Pg doesn't
simply fallback to client-side prepare when a server-side prepare
can't be performed. I believe DBD::mysql does that.

It's a safety feature. :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:

On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:

WARNING: DBD::Pg now (as of version 1.40) uses true prepared
statements by sending them to the backend to be prepared by the
Postgres server. Statements that were legal before may no longer
work.

Sure seems like a bug, or at best a misfeature, that DBD::Pg doesn't
simply fallback to client-side prepare when a server-side prepare
can't be performed. I believe DBD::mysql does that.

It's a safety feature. :)

Er. I see the smiley but I'm not sure if that's a joke. Can you expand?

Tim.

On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:

On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:

WARNING: DBD::Pg now (as of version 1.40) uses true prepared
statements by sending them to the backend to be prepared by
the Postgres server. Statements that were legal before may no
longer work.

Sure seems like a bug, or at best a misfeature, that DBD::Pg
doesn't simply fallback to client-side prepare when a
server-side prepare can't be performed. I believe DBD::mysql
does that.

It's a safety feature. :)

Er. I see the smiley but I'm not sure if that's a joke. Can you
expand?

It's not a joke. Client-side prepare is essentially creating a
duplicate code path and hoping that it does exactly the same thing
that the server-side one does, and this in a context of controlling
access.

If PostgreSQL's parser, etc., were in the form of exportable
libraries, that would be very nice, but until then, making server-side
prepare the only kind is just jungle caution.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Thu, May 07, 2009 at 06:08:12PM -0700, David Fetter wrote:

On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:

On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:

WARNING: DBD::Pg now (as of version 1.40) uses true prepared
statements by sending them to the backend to be prepared by
the Postgres server. Statements that were legal before may no
longer work.

Sure seems like a bug, or at best a misfeature, that DBD::Pg
doesn't simply fallback to client-side prepare when a
server-side prepare can't be performed. I believe DBD::mysql
does that.

It's a safety feature. :)

Er. I see the smiley but I'm not sure if that's a joke. Can you
expand?

It's not a joke. Client-side prepare is essentially creating a
duplicate code path and hoping that it does exactly the same thing
that the server-side one does, and this in a context of controlling
access.

If PostgreSQL's parser, etc., were in the form of exportable
libraries, that would be very nice, but until then, making server-side
prepare the only kind is just jungle caution.

So you're okay with breaking previously working, and prefectly valid, DBI code?

And you're okay with forcing application writers to "know" which kinds
of sql statements can, or can't, be server-side prepared by the
particular version of postgress they're talking to?

From the DBI's perspective, $dbh->prepare($valid_sql_statement) should
always work.

Tim.

Tim Bunce wrote:

So you're okay with breaking previously working, and prefectly valid,

DBI code?

I think the rationale is that such code was working by virtue of how
prepare() was implemented in DBD::Pg, but was not "valid" nonetheless,
as outlined with this example:
http://archives.postgresql.org/pgsql-general/2005-11/msg00339.php

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

On Fri, May 08, 2009 at 09:44:56AM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 06:08:12PM -0700, David Fetter wrote:

On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:

On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:

On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:

WARNING: DBD::Pg now (as of version 1.40) uses true
prepared statements by sending them to the backend to be
prepared by the Postgres server. Statements that were
legal before may no longer work.

Sure seems like a bug, or at best a misfeature, that DBD::Pg
doesn't simply fallback to client-side prepare when a
server-side prepare can't be performed. I believe
DBD::mysql does that.

It's a safety feature. :)

Er. I see the smiley but I'm not sure if that's a joke. Can
you expand?

It's not a joke. Client-side prepare is essentially creating a
duplicate code path and hoping that it does exactly the same thing
that the server-side one does, and this in a context of
controlling access.

If PostgreSQL's parser, etc., were in the form of exportable
libraries, that would be very nice, but until then, making
server-side prepare the only kind is just jungle caution.

So you're okay with breaking previously working, and prefectly
valid, DBI code?

That's not the kind of code it broke.

And you're okay with forcing application writers to "know" which
kinds of sql statements can, or can't, be server-side prepared by
the particular version of postgress they're talking to?

They need to know what kinds of SQL statements are valid, full stop.

From the DBI's perspective, $dbh->prepare($valid_sql_statement)
should always work.

Yes, it should, and unless and until PostgreSQL's parser becomes an
exportable library, there will be no way to establish that on the
client side.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Fri, May 08, 2009 at 04:02:29PM +0200, Daniel Verite wrote:

Tim Bunce wrote:

So you're okay with breaking previously working, and prefectly valid,

DBI code?

I think the rationale is that such code was working by virtue of how
prepare() was implemented in DBD::Pg, but was not "valid" nonetheless, as
outlined with this example:
http://archives.postgresql.org/pgsql-general/2005-11/msg00339.php

It's perfectly valid (from the DBI's point of view) for prepare() to
return a prepared statement handle for an invalid statement.

It's not the job of prepare() to validate the SQL. It's a bonus if it
does, but the primary goal is "to prepare as much as possible" for
future execution.

There are *many* DBI drivers that can't/don't validate the SQL on
prepare. DBD::Oracle, for example, can but doesn't by default.
It defers the prepare until the first execute (or meta data is
requested) in order to reduce the number of round-trips.

The example that started this thread was that this valid statement
worked:

prepare("CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT INTO foo(2, 2);")

but this valid statement didn't:

prepare(" INSERT INTO foo(1, 1); INSERT INTO foo(2, 2);")

My argument is that both calls should return statement handles.

The DBI user should not be exposed to the inner-workings and limitations
of the support for server-side prepare.

If a server-side prepare is attempted and fails because it's a kind of
statement that can't be server-side prepared then DBD::pg should
fallback to a client-side prepare. It does not matter that this may mean
some invalid statements are caught by prepare() and others by execute().
The DBI spec has always allowed for that.

Tim.

p.s. I'd be happy to see 'success with info' status returned if the
prepare() has to unexpectly fallback to client-side (and perhaps a dbh
counter incremeted). So users can tell when and how often it's happening
if they want to.

Tim Bunce wrote:

The example that started this thread was that this valid statement
worked:

prepare("CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT

INTO foo(2, 2);")

but this valid statement didn't:

prepare(" INSERT INTO foo(1, 1); INSERT

INTO foo(2, 2);")

My argument is that both calls should return statement handles.

I think they do, and the original report is somehow flawed. Here's a
test that demonstrates this with the SQL pasted from the initial
example.

print "version is $DBD::Pg::VERSION\n";
$dbh->{pg_server_prepare} = 1;
my $prepare_sql =<<SQL;
CREATE TEMP TABLE foo( id int, user_id int,);

INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL
my $sth1=$dbh->prepare($prepare_sql);
print "1st statement handle=$sth1\n";
$prepare_sql=<<SQL;
INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL
my $sth2=$dbh->prepare($prepare_sql);
print "2nd statement handle=$sth2\n";

And here's the output I get:
version is 2.8.2
1st statement handle=DBI::st=HASH(0x8d40908)
2nd statement handle=DBI::st=HASH(0x8c73660)

If a server-side prepare is attempted and fails because it's a kind

of

statement that can't be server-side prepared then DBD::pg should
fallback to a client-side prepare.

Unfortunately with PG, an error in server-side prepare aborts the
current transaction, so that any subsequent command will fail until a
rollback is issued. Falling back to client-side prepare once in this
state would probably not help much.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

Daniel Verite wrote:

Tim Bunce wrote:

The example that started this thread was that this valid statement
worked:

prepare("CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT

INTO foo(2, 2);")

but this valid statement didn't:

prepare(" INSERT INTO foo(1, 1); INSERT

INTO foo(2, 2);")

My argument is that both calls should return statement handles.

I think they do, and the original report is somehow flawed.

In my attempt to obfuscate the actual code, I actually included invalid
SQL , but I can assure you that the failure occurred as I described it,
though only with the version 2.11.8. Other versions > 1.4 worked fine,
despite the explanation in the DBD::Pg docs which implied that they
might not.

Here's a test that demonstrates this with the SQL pasted from the
initial example.

print "version is $DBD::Pg::VERSION\n";
$dbh->{pg_server_prepare} = 1;
my $prepare_sql =<<SQL;
CREATE TEMP TABLE foo( id int, user_id int,);

INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL
my $sth1=$dbh->prepare($prepare_sql);
print "1st statement handle=$sth1\n";
$prepare_sql=<<SQL;
INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL
my $sth2=$dbh->prepare($prepare_sql);
print "2nd statement handle=$sth2\n";

And here's the output I get:
version is 2.8.2
1st statement handle=DBI::st=HASH(0x8d40908)
2nd statement handle=DBI::st=HASH(0x8c73660)

If a server-side prepare is attempted and fails because it's a kind

of

statement that can't be server-side prepared then DBD::pg should
fallback to a client-side prepare.

Unfortunately with PG, an error in server-side prepare aborts the
current transaction, so that any subsequent command will fail until a
rollback is issued. Falling back to client-side prepare once in this
state would probably not help much.

Best regards,

--
JP Fletcher
Database Administrator
Afilias Canada
voice: 416.646.3304 ext. 4123
fax: 416.646.3305
mobile: 416.561.4763
jpfletch@ca.afilias.info

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

It's perfectly valid (from the DBI's point of view) for prepare() to
return a prepared statement handle for an invalid statement.

It's not the job of prepare() to validate the SQL. It's a bonus if it
does, but the primary goal is "to prepare as much as possible" for
future execution.

There are *many* DBI drivers that can't/don't validate the SQL on
prepare. DBD::Oracle, for example, can but doesn't by default.
It defers the prepare until the first execute (or meta data is
requested) in order to reduce the number of round-trips.

Just to set the record straight on this thread, DBD::Pg also defers
actual preparation until needed (e.g. the first execute) and thus
will accept all prepare statements, regardless of what is inside
of them. It does this not only to save trips, but because bind_param
may be called betwixt the prepare and the execute.

This works fine with DBD::Pg:

$sth = $dbh->prepare('foobar');

However, this will then fail:

$sth->execute();

Thus, as pointed out elsewhere, the original report was not correct:
the error comes when execute() is called, not when prepare() is.

p.s. I'd be happy to see 'success with info' status returned if the
prepare() has to unexpectly fallback to client-side (and perhaps a dbh
counter incremeted). So users can tell when and how often it's happening
if they want to.

For DBD::Pg, this is not possible as prepare always works. Even if it didn't,
I think the fallback idea is not a good one, as we'd be potentially creating
many errors to have to rollback to (and creating a savepoint for every execute
attempt), and silently encouraging SQL that will fail when fed to Postgres
through any other interface.

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

iEYEAREDAAYFAkpcpGMACgkQvJuQZxSWSsjr1ACgjLWM4EurT9V+wXLXO83A+7pE
BPYAoKfYCl+6ywnOoQW4OOlKEP1YID0D
=QmVQ
-----END PGP SIGNATURE-----