Thread: prepared statements and DBD::Pg
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?
--
JP Fletcher
Database Administrator
Afilias Canada
voice: 416.646.3304 ext. 4123
fax: 416.646.3305
mobile: 416.561.4763
jpfletch@ca.afilias.info
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-----