On Tue, Dec 11, 2012 at 10:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This is why the pg_dump master process executes a
>> lock table <table> in access share mode
>> for every table, so your commands would all block.
>
> A lock doesn't protect against schema changes made before the lock was
> taken. The reason that the described scenario is problematic is that
> pg_dump is going to be expected to work against a snapshot made before
> it gets a chance to take those table locks. Thus, there's a window
> where DDL is dangerous, and will invalidate the dump --- perhaps without
> any warning.
>
> Now, we have this problem today, in that pg_dump has to read pg_class
> before it can take table locks so some window exists already. What's
> bothering me about what Andres describes is that the window for trouble
> seems to be getting much bigger.
It would be sort of nice to have some kind of lock that would freeze
out all DDL (except for temporary objects, perhaps?). Then pg_dump
could take that lock before taking a snapshot. As a nifty side
benefit, it would perhaps provide a way around the problem that
databases with many relations are undumpable due to lock table
exhaustion.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company