Re: LOCK for non-tables - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Jan 11, 2011 at 4:46 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> For query based replication tools like pgpool-II (I don't know any
>> other tools, for example Postgres XC falls in this category or
>> not...), we need to be able to lock sequences. Fortunately it is allowed to:
>> 
>> SELECT 1 FROM foo_sequece FOR UPDATE;
>> 
>> but LOCK foo_sequence looks more appropreate syntax for me.

> Those aren't doing the same thing.  The first is locking the one and
> only tuple that is contained within the sequence, while the second is
> locking the sequence object itself.

> At this point, I'm inclined to think that the pg_dump comment is just
> wrong, and we ought to fix it to say that we don't really want to be
> able to lock other relations after all, and call it good.

The reason that pg_dump tries to acquire locks at all is to ensure that
it dumps a consistent view of the database.  The general excuse for not
locking non-table objects is that (at least in most cases) they are
defined by single catalog entries and so there's no way to see a
non-self-consistent view of them.  Tables, being defined by a collection
of rows in different catalogs, are *very* risky to dump without any
lock.  This doesn't get noticeably better for non-table relation types.

An example of the sort of risk I'm thinking about is dumping a view
without any lock while someone else does a CREATE OR REPLACE VIEW on
it.  You could very easily see a set of attributes (in pg_attribute)
that don't agree with the view rules you pulled from pg_rewrite.  The
risk is minimal now since we don't allow C.O.R.V. to change the column
set, but as soon as somebody creates a patch that allows that, pg_dump
will have a problem.

Note that using a serializable transaction (with or without "true"
serializability) doesn't fix this issue, since pg_dump depends so
heavily on backend-side support functions that work in SnapshotNow mode.
It really needs locks to ensure that the support functions see a view
consistent with its own catalog reads.

In the SEQUENCE example above, SELECT ... FOR UPDATE is certainly not
adequate to protect the sequence against DDL-level changes.  Fortunately
sequences don't have too many DDL commands, but still an ALTER RENAME
might be enough to confuse pg_dump.

(By the way, does that SELECT ... FOR UPDATE actually accomplish
anything at all?  nextval() doesn't go through heap_update, and neither
does ALTER SEQUENCE, so I'd be a bit surprised if it really manages to
block changes to the sequence.)
        regards, tom lane