Re: ERROR: prepared statement "dbdpg_p13754_3" does not exist

On Fri, Nov 21, 2008 at 05:25:10PM -0000, Greg Sabino Mullane wrote:
> Can you tell us what versions of DBI, DBD::Pg, and Postgres you are using?
> Can you narow it down to a reproducibale test case? Barring that, you
> might try turning on some tracing to see what is going on. Just the SQL
> level should at least show you where the prepared statement is getting
> created, called, and destroyed. You can send it to a file to not disrupt
> your application with something like this:
> 
> $dbh->trace('SQL', '/tmp/dbdpg.debug.log');


Here's an example.  Running a Catalyst application which has an eval
block around every request.  And likewise, I've wrapped the failing
select in an eval.

From the trace at the bottom sure looks like the prepare is happening
(and failing) and then there's no "execute" as the error log implies.
Then on the second call it's just calling execute.

I assumed that the ALTER had something to do with it, but it seems
like commenting that out will generate the same results.



 PostgreSQL 8.2.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)




moseley@bumby2:~$ rm dbdpg.debug.log

moseley@bumby2:~$ cat prepare.pl
#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect(
    'dbi:Pg:', '', '',
    {
        RaiseError => 1,
        AutoCommit => 1,
    },
);
$dbh->trace('SQL', 'dbdpg.debug.log');

warn "DBI Version: $DBI::VERSION\nDBD::Pg Version: $DBD::Pg::VERSION\n";

eval { $dbh->do( 'DROP TABLE test_table' ) };

$dbh->do( <<SQL );
    CREATE table test_table (
        id      SERIAL PRIMARY KEY,
        foo     integer,
        bar     text
    )
SQL


eval { run_select() };
warn "after eval\n";

$dbh->do( 'SELECT version()' );

$dbh->do( 'ALTER TABLE test_table ADD COLUMN baz boolean' );

warn "Running second time\n";
run_select();


sub run_select {
    my $sth = $dbh->prepare_cached( <<SQL );
        SELECT id, foo, bar, baz FROM test_table WHERE id = ?
SQL

    $sth->execute( 2 );

    return $sth->rows;
}





moseley@bumby2:~$ ./prepare.pl
DBI Version: 1.605
DBD::Pg Version: 2.8.2
NOTICE:  CREATE TABLE will create implicit sequence "test_table_id_seq" for serial column "test_table.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_table_pkey" for table "test_table"

DBD::Pg::st execute failed: ERROR:  column "baz" does not exist
LINE 1:         SELECT id, foo, bar, baz FROM test_table WHERE id = ...
                                     ^ at ./prepare.pl line 44.
after eval
Running second time
DBD::Pg::st execute failed: ERROR:  prepared statement "dbdpg_p17810_1" does not exist at ./prepare.pl line 44.
DBD::Pg::st execute failed: ERROR:  prepared statement "dbdpg_p17810_1" does not exist at ./prepare.pl line 44.


moseley@bumby2:~$ cat dbdpg.debug.log 
DROP TABLE test_table;

    CREATE table test_table (
        id      SERIAL PRIMARY KEY,
        foo     integer,
        bar     text
    )
;

PREPARE dbdpg_p17810_1 AS         SELECT id, foo, bar, baz FROM test_table WHERE id = $1
;

SELECT version();

ALTER TABLE test_table ADD COLUMN baz boolean;

EXECUTE dbdpg_p17810_1 (
$1: 2
);

    >> DESTROY DBI::db=HASH(0x866aac0) clearing 1 CachedKids






-- 
Bill Moseley
[email protected]
Sent from my iMutt