From: | Emi Lu <emilu(at)encs(dot)concordia(dot)ca> |
---|---|
To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: pg_restore in java connection (auto_commit = false) |
Date: | 2008-09-11 13:48:36 |
Message-ID: | 48C921B4.5060902@encs.concordia.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> 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!
From | Date | Subject | |
---|---|---|---|
Next Message | Ruben Gouveia | 2008-09-11 17:33:13 | DIfference between max() and greatest() ? |
Previous Message | Igor Neyman | 2008-09-11 12:43:04 | Re: Aggregates in WHERE clause? |