Re: prepared statements and DBD::Pg

    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