Continued problems with pgdump, Large Objects and crashing backends - Mailing list pgsql-hackers

[I'm posting this to the hackers list, as I think this is something deep
in the backend, and not JDBC - Peter]

I've been talking to Jason Venner <jason@idiom.com> over the last couple
of days with an interesting problem.

He's got a small Java application that restores large objects from a
backup to a database. However, the backend seemed to segv at exactly the
same moment.

This occurs with both 6.3.x and 6.4.x (can't remember what revision).

Last night, he sent me a copy of the app, and I ran it against a recent
(last Saturday) cvs copy of 6.5, and the same thing happens.

Now to good bit ;-)

The first problem is with pgdump. His restore app is in two parts, a shell
script and a Java application. The shell script creates two databases
(edit and prod), restores them (from the output of pgdump), then calls
Java to load the large objects, and repair the differing oid's.

However, this fails when creating functions that have more than one sql
statement in them. He has some functions that insert into a table
depending on some arguments, then issue a select on the last arg which is
the functions result. However, pgdump doesn't end the select with a ; and
this causes the 6.5 backend to fail. Adding the ; fixes the problem.

I don't know if it's a known problem, but may need someone to check.

Ok, that's the simple one. Now that harder two:

When the Java app runs, it causes the backend to segv repeatedly at the
same point (after about the 60th large object). Now, I've checked his code
and can't find anything obviously wrong, so I added some tracing to it,
and discovered that when the application closes (either explicitly closing
the application, or upon an error), the backend outputs the following to
stderr, and segv's:

pq_recvbuf: recv() failed, errno 2
pq_recvbuf: recv() failed, errno 0

Now, each of these are for the two open connections, and each appears as
soon as it's respective connection closes. Remember the connections are to
two different databases.

Running the backed with the -d2 flag, these expand to:

pq_recvbuf: recv() failed, errno 2
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 6731 exited with status 0
pq_recvbuf: recv() failed, errno 0
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 6730 exited with status 0

This is repeatable, and is not related at all to the large object being
loaded. Reversing the order that the objects are loaded, causes it to fail
on a different object.

Now, the first question: Does someone who knows the backend better than I
do know what could cause the recv() message to occur when disconnecting?

Now the third problem. The last problem occurs outside of any
transactions. In JDBC, you use transactions by setting autocommit to
false. Then, there are methods to commit or rollback the database.

Ok, now the problem. When he sets autocommit to false, the JDBC driver
sends BEGIN to the backend. Ok so far, however, something then fails
during the first large object's load, and causes everything else to fail.

I haven't looked into this one fully, but it's identical on all three
major versions of the backend, which is a little surprising.

Now the weird thing is that the same errors occur when the connections are
closed.

I don't think it's a JDBC problem, as I can't reproduce it with any of my
code. Neither can I see anything wrong with Jason's code.

Any how, this is what's kept me busy the last few evenings, and it's got
be stumped.

Peter

--       Peter T Mount peter@retep.org.uk     Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgresJava PDF Generator: http://www.retep.org.uk/pdf