Re: LOCK for non-tables

On Tue, Jan 11, 2011 at 10:35 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Jan 11, 2011 at 4:46 AM, Tatsuo Ishii <ishii(at)postgresql(dot)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.

Actually, we do allow C.O.R.V. to do just that - I believe since 8.4.

rhaas=# create view v(a) as select 1;
CREATE VIEW
rhaas=# create or replace view v(a,b) as select 1, 2;
CREATE VIEW

> 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 that case, can I have some comments on approaches mentioned in my OP?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company