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