Re: Logical decoding & exported base snapshot

On 2012-12-13 17:11:31 -0500, Robert Haas wrote:
> On Tue, Dec 11, 2012 at 10:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

That would solve the consistency problem, yes. Would we need a special
kind of permission for this? I would say superuser/database owner only?

It should actually even work for standbys in contrast to my FOR SHARE
hack idea as we could "just" make it conflict with the exclusive locks
logged into the WAL.

I don't think that it will against the too-many-locks problem itself
though, unless we play some additional tricks. The locks will be
acquired later when the tables are dumped anyway. Now, given the
snapshot import/export feature we actually could dump them table by
table in a single transaction, but thats a bit more complicated.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services