Re: Enhancement to pg_dump

"Rob Kirkbride" <rob(dot)kirkbride(at)gmail(dot)com> writes:

> I must admit I've not read up on the various locks that are set so that's a
> good point. Is there a good reference for me to read and understand these?
>
> I'm guessing though that a delete from and then an insert never requires an
> exclusive lock, what about adding/deleting constraints?

There is documentation

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

However I found it very confusing when I was first learning. It's not really
the documentation's fault either, there are just a lot of different lock
levels with a lot of different combinations possible.

All DML, even selects, take a table-level shared lock on the tables involved
which blocks the tables from being dropped or truncated while the query is
running.

DELETE and UPDATE (and SELECT FOR UPDATE) take exclusive row-level locks. A
SELECT can read the old version of the record but another UPDATE will block
until your transaction finishes so it can update the most recent version. But
an update which doesn't need to look at that record won't be affected at all.

TRUNCATE and DROP take exclusive table-level locks which blocks anyone else
from even selecting from the table. It also means they can't proceed until all
queries which have already started reading the table finish.

DROP is still a lot heavier than TRUNCATE because it also has to drop (or
search for and throw an error) anything else dependent on the table. triggers,
views, etc.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!