Thread: DBD::Pg returns "DBD::Pg::st execute failed: connection not open"

Hello all,

I'm having an odd issue and was wondering if someone could point me in
the right direction.  I hope this is also on the correct list.  If
not, let me know.

I'm using Perl 5.8.8 with DBD-Pg-1.49.  I have a perl script which
periodically inserts information into a database.  All of this has
been working fine for ages.  But very recently, and for some unknown
reason, my script stops working, the error being:

DBD::Pg::st execute failed: connection not open

I had previously set DBI->trace(4) and found out that my script was
able to connect just fine to the database, and perform SELECT queries,
but as soon as it came to running an INSERT query, the script
segfaults [1].

Now, at this point, I am confused.  I know, because I have tested it
on another machine, that the data in question works just fine.
Neither, does this issue seem to revolve around the data, as far as I
can work out, this "error" is completely intermittent.  But as soon as
it happens, it's fatal.  Even after I clean out the postgres database
(i.e., delete all the files pertaining to that database) and rebuild
it, the error still remains.

This does suggest to me that there's some weird interaction between
the DBD-Pg libraries and the underlying C code?  Has anyone got any
idea what might be going on?  The closest piece of information I have
is in this post:

http://archives.postgresql.org/pgsql-general/2000-02/msg00205.php

Although unlike him, I am actually able to perform SELECT/INSERT
queries via psql after this "connection not open" error just fine ---
which does suggest to me more that something is amiss with the DBD-Pg
<-> C interaction somewhere.

Can anyone shed any light on this?   If you need further information,
don't hesitate to ask, and I'll see what I can do.

Kindly,

Thomas Adam

[1]  Well, the *perl* does, at any rate.  Whether or not it's the
postgres daemon which kicks it out, or the client itself is unclear.
I'm going to guess the client, since the daemon still runs.

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


> I'm using Perl 5.8.8 with DBD-Pg-1.49.  I have a perl script which
...
> I had previously set DBI->trace(4) and found out that my script was
> able to connect just fine to the database, and perform SELECT queries,
> but as soon as it came to running an INSERT query, the script
> segfaults [1].

Most likely a DBD::Pg bug. Some ideas:

1) Try out the new version of DBD::Pg and see if it helps. If so, it's 
most likely an already-fixed bug.

http://search.cpan.org/dist/DBD-Pg/ - the 2.0.0_x one

2) Try setting trace to 10 and see if you can figure out the last thing it 
did before dying.

3) Use gdb to see what it's dying on. Run "gdb perl core" after it coredumps 
and do a "backtrace".


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200802050023
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFHp/NJvJuQZxSWSsgRAxDmAKDSrHAK/FFwxnd6eXQPBHQ4p+sEmwCgovOP
SOiERoAlhKoaKBrXO7ZMY8s=
=E9YC
-----END PGP SIGNATURE-----

Hello Greg --

On 05/02/2008, Greg Sabino Mullane <greg@turnstep.com> wrote:
> Most likely a DBD::Pg bug. Some ideas:

Before we get to the particulars, I appreciate your assistance on
this.  I am rapidly coming to the same conclusion that there's either
a bug in DBD::Pg itself, or (dare I even suggest it) DBI itself.

> 1) Try out the new version of DBD::Pg and see if it helps. If so, it's
> most likely an already-fixed bug.

I used DBD::Pg version 2.0.0_9 (seemed to be the latest) and even
bumped my version of Postgres to 8.3.0.  Still the same error, alas.

> 2) Try setting trace to 10 and see if you can figure out the last thing it
> did before dying.

OK.  Here's the DBI->trace(10) output:

http://edulinux.homeunix.org/~n6tadam/pg-dbd-debug.txt

There's two things going on here, and it's the first thing which is
drawing my attention (and exhibiting the error in question).  All the
lines up to line 1780 show that the program is correctly executing
SELECT statements and returning values just fine.  This is all normal
and expected.

Line 1780 shows the query as it is passed to PQexec.  You'll note the
following output after that:

dbdpg: _sqlstate PQresultStatus returned 7
dbdpg: _sqlstate set imp_dbh->sqlstate to 22000
dbdpg: _sqlstate txn_status is 4
dbdpg: Invalid status returned (7)
dbdpg: pg_error (server closed the connection unexpectedly    This probably means the server terminated abnormally
beforeor while processing the request.) number=7
 
dbdpg: sqlstate 22000 error_num 7 error server closed the connection
unexpectedly

I've looked briefly through the DDB::Pg code and noticed that an error
return of 22000 indicates that it's either as a result of
PGRES_FATAL_ERROR or something else -- but I cannot get any
information further about what the PGRES_FATAL_ERROR might entail,
either from the trace output or a literal strace of the postgres
processes.

Note that taking the query verbatim from line 1780 and pasting it into
psql inserts it correctly without any error.

Taking this further, I then decided that I couldn't really get much
more information from the trace output (hopefully you can though!) so
I then decided to tcpdump what was on port 5432.  What I found via
wireshark's analysis is that there's something odd (at times) between
the connection/disconnection mechanism.  What should happen is
FIN/ACK/RET get sent to and from the client, but what I have seen with
this problem is that the connection is closed (from the client), the
server says it's received that (ACK) and closed the connection, but
the client then decides to try and INSERT data anyway.

Why this would be, I do not know, nor why it should happen.  It could
be a red-herring for all I know, but I thought I would mention it.

> 3) Use gdb to see what it's dying on. Run "gdb perl core" after it coredumps
> and do a "backtrace".

Sorry --- but it crashing isn't happening on my test machine, alas.
It just exists abnormally without segfaulting.

What's the next step, Greg?  I'm really at a loss here as to what's
going on.  If you need any further information from me, do let me
know.  If you can provide a pointer as to where I might look to help
you debug this, do also let me know.

-- Thomas Adam