Re: prepared statements and DBD::Pg

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.