Re: pgpool versus sequences

On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Tatsuo Ishii's message of mié jun 01 19:08:16 -0400 2011:
>> What pgpool really wanted to do was locking sequence tables, not
>> locking rows in sequences. I wonder why the former is not allowed.
>
> Yeah -- why is LOCK SEQUENCE foo_seq not allowed?  Seems a simple thing
> to have.

It cause a grammar conflict. Since SEQUENCE and NOWAIT are both
unreserved keywords, it's not clear to the parser whether "LOCK
SEQUENCE NOWAIT" means to lock a table called SEQUENCE without
waiting, or whether it means to lock a sequence called NOWAIT.

Tom and I discussed possible ways of fixing this on -hackers a few
months ago. Currently the syntax for LOCK is:

LOCK [ TABLE ] [ ONLY ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ];

I suggested fixing this by making TABLE required, thus:

LOCK TABLE [ ONLY ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ];

Tom suggested fixing it by making NOWAIT require IN lockmode MODE, thus:

LOCK [ TABLE ] [ ONLY ] name [,...] [ IN lockmode MODE [ NOWAIT ]];

My proposed fix is probably more likely to break people's
applications, but Tom's isn't completely free from that possibility
either. It's also somewhat counterintuitive IMV. The best option
might be to come up with some completely new syntax that is a little
better designed than the current one, maybe along the lines of the
extensible-options syntax used by EXPLAIN. The trouble is that the
first word of the command would probably have to be something other
than LOCK if we don't want to break backward compatibility with the
existing syntax in some way, and there aren't too many good synonyms
for LOCK. LATCH? FASTEN? Blech. We're probably going to end up
having to make a compatibility break here if we want to support this.

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