LOCK for non-tables - Mailing list pgsql-hackers

One of the things that I ripped out of the SQL/MED syntax patch before
committing it was the hack that made LOCK TABLE also work on FOREIGN
TABLEs.  Since we're treating a foreign table as a different kind of
object than a TABLE in some places, we shouldn't confuse the two
things elsewhere, at least in my opinion.  I also noticed that pg_dump
has the following comment:
                * NOTE: it'd be kinda nice to lock other relations
too, not only                * plain tables, but the backend doesn't presently allow that.

This is pretty trivial from a backend point of view, especially with
the new objectaddress.c machinery.  In a comparatively small amount of
code we could support locks not only on all types of relations but
also on any other backend objects we think it's worth supporting.  The
only difficulty is the grammar: we allow either "LOCK relname" or
"LOCK TABLE relname", so adding e.g. "LOCK SEQUENCE relname" creates a
shift/reduce conflict because SEQUENCE is unreserved.  We can easily
make "LOCK FOREIGN TABLE relname" work because FOREIGN is already
full-reserved, but that seems to be the only case that can be done
relatively painlessly.

So, the options as I see them are:

1. Do nothing.
2. Support LOCK FOREIGN TABLE relname and forget about the rest.
Feels fairly arbitrary, but avoids any hard decisions.
3. Partially reserve keywords like VIEW and SEQUENCE, and support LOCK
[ TABLE | VIEW | SEQUENCE | FOREIGN TABLE ] relname.  Doesn't really
scale to other object types unless you keep reserving more keywords,
but maybe we don't care.
4. Make the keyword TABLE required, and support LOCK { TABLE | VIEW |
SEQUENCE | FOREIGN TABLE | maybe other object types } relname.  This
is a backward-compatibility break, but so is reserving keywords, and
this approach has two redeeming virtues: (1) it only affects people
who are actually using "LOCK foo", whereas partially reserving
keywords will affect people using completely unrelated parts of the
system, and (2) it's pretty much future-proof - we can add more
relkinds or other object types down the road with no additional pain.
5. Create some alternative syntax for locking, and continue to support
the existing syntax for backward compatibility.  We've done this
successfully with COPY (twice), EXPLAIN, and VACUUM, but it's not
clear how to do it here.  You'd need either a different verb (and it's
not too clear what would make half as much sense as LOCK) or else a
syntax that involved putting something that can't be confused with a
table name immediately afterward.  Something like LOCK (SEQUENCE foo)
would work, but that seems unspeakably ugly.  LOCK IN [mode] { TABLE |
VIEW | SEQUENCE | FOREIGN TABLE | maybe other object types } relname
would work too, but that seems ugly and confusing also.  Nothing else
is coming to mind at the moment.

On balance I think my vote is for #4.  Other votes/ideas?

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