Re: pg_restore in java connection (auto_commit = false)

> 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.

This is exactly what I was trying to make it clear to myself.
Thank you Craig!