Re: pg_restore in java connection (auto_commit = false)

Emi Lu wrote:
> Good afternoon,
>
> I have a question about pg_restore in JAVA and need your help.
>
> I planed to do:
> ==================
> 1. pg_dump each tables (done successfully)
> 2. start a java connection
> auto_commit = false
>
> 3.for(Table1... ... TableN)
> {
> drop primary key
>
> pg_restore table (dead lock here)
>
> add primary key
>
> Connection.commit
> }

I suspect I see what's going on (see below) but let's jump back a step
or two first and look at what you're actually doing.

What are you trying to achieve with this? What is the end goal, the
problem to be solved?

Why are you dumping tables individually then restoring them individually
in your code? Do you want to selectively restore only a subset of your
tables?

Are you actually invoking the "pg_dump" and "pg_restore" commands via
Runtime.exec() or similar? Or are you emulating their function in your
Java code? (I'm assuming the former, but have to ask anyway).

> It seems that the above steps caused a lock for the tableX. Could
> someone give me some ideas please?

If I'm guessing correctly from your fairly vague post, I'd say you're
actually executing an instance of the pg_restore process while holding
an open transaction on the table. Your connection via (presumably) JDBC
obtains an ACCESS EXCLUSIVE lock on the table when it issues the ALTER
TABLE ... DROP CONSTRAINT to drop the primary key. Assuming that's what
you're doing, since you didn't actually show your commands.

See:

http://www.postgresql.org/docs/8.3/interactive/explicit-locking.html

where it says that ALTER TABLE obtains the ACCESS EXCLUSIVE lock. You
can confirm this by issuing the command of interest then running:

SELECT * from pg_catalog.pg_locks;

With that transaction still open, and that lock still held, you then
execute a new process (pg_restore) that establishes its own unrelated
connection to the database and tries to get a ROW EXCLUSIVE lock (if
using INSERT, and presumably COPY though the docs don't say so) on the
table. It can't do so, because your Java program holds an ACCESS
EXCLUSIVE lock on the table that conflicts with the requested lock mode.

Your java code won't release the lock until pg_restore finishes, and
pg_restore won't finish until your java code releases the lock.

Deadlock.

There is no way you can "pass" your connection to pg_restore when you
invoke it from Java. Thus, you must either not hold any locks that would
prevent pg_restore from acting on the table, or you must do all the work
within Java using your existing JDBC connection.

Personally, I strongly suspect that the latter is by far the best
option. Shelling out to database admin tools isn't a great idea, as
their paths will vary, their names might even vary in some installs,
etc. If you need to dump and restore within Java, do it from within Java.

Personally I'd want to dump the tables into a Java-friendly serialized
format that I could read back in later and transform into multi-valued
INSERT statements. Another option that might be easier would be to use a
tool like Hibernate that can take care of the data serialization for you.

This all depends, of course, on whether there's a better way to achieve
what you're trying to do in the first place that doesn't involve all
this per-table dump and restore hassle at all.

--
Craig Ringer