From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Tatsuo Ishii <ishii(at)postgresql(dot)org>, fgp(at)phlo(dot)org, simon(at)2ndquadrant(dot)com, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: LOCK for non-tables |
Date: | 2011-01-11 15:35:43 |
Message-ID: | 18104.1294760143@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Florian Pflug | 2011-01-11 15:48:54 | Re: Bug in pg_describe_object |
Previous Message | Joel Jacobson | 2011-01-11 15:31:32 | Re: Bug in pg_describe_object |