Re: Enhancement to pg_dump

Gregory Stark wrote:
> 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.
>
>

Thanks for that - it's very useful. As you say I believe the
documentation is pretty good, it's just that we're not dealing in simple
issues here.

I definitely think I should do a delete rather than a truncate (or drop)
in my case.

Regards

Rob