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

On Tue, Jan 11, 2011 at 4:46 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> On Fri, Jan 7, 2011 at 6:28 PM, Florian Pflug <fgp@phlo.org> wrote:
>>> I forgot about sequences earlier. If we dump while someone deletes all
>>> rows and resets the sequence the dump might contain rows and still
>>> reset the sequence. This could cause duplicate key errors on restore.
>>> I haven't checked if this is really possible though - I guess it would
>>> depend on the exact order of these events...
>>
>> To fix this, you'd need a lock that allowed getting values from the
>> sequence but prevented resetting it, and...
>>
>>> I wonder how such locks would work. Would such locks prevent accessing
>>> these objects? Or just modifications? For example, if I locked a function,
>>> could someone else execute it while I held the lock?
>>
>> ...in fact we do very little locking of objects other than tables.
>> DROP takes an AccessExclusiveLock on whatever it's dropping, and
>> COMMENT and SECURITY LABEL take ShareUpdateExclusiveLocks to avoid
>> orphaning pg_{sh,}description or pg_seclabel entries in the face of a
>> concurrent drop, but most operations on non-table objects don't AFAIK
>> take any lock at all.  We probably don't want to make too many changes
>> in this area, because there are already workloads where the
>> heavyweight lock manager can become a bottleneck, and one can easily
>> imagine that locking operators or namespaces could make that problem
>> much worse.
>
> 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.

As a side node, locking a sequence for replication seems like it could
have pretty dire effects on performance in certain workloads.  Why do
you need to do that, anyway?

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