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

On Fri, Jan 7, 2011 at 12:17 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Fri, 2011-01-07 at 08:16 -0500, Robert Haas wrote:
>
>> 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.
>
> I'm not clear why we'd want to do that. We shouldn't just be adding
> things because we can do them easily, we should be adding things with a
> clear use case or a standardization requirement.

Good point.

The reason why the pg_dump comment suggests that this feature would be
useful is that it would allow locking relations, and possibly other
objects, against concurrent drops.  pg_dump can offer a
mostly-consistent view of the contents of every table in the system by
taking a snapshot at the beginning of operation and using that same
snapshot to completion.  But that won't necessarily give it a
consistent view of the system catalogs, because those generally follow
SnapshotNow rules.  To be fully consistent, it needs to take
AccessShareLocks on any schema objects it looks at.  Currently, it can
do that for tables, but not other object types.

So suppose you pg_dump a view and and a function that uses the view.
In the middle of the dump, someone alters the view and the function in
a single transaction and commits it.  You might dump the function
before the transaction commits and the view afterward, or visca versa,
and the result will be an inconsistent view of the database schema.
Allowing pg_dump to take AccessShareLocks on the objects in question
would prevent this sort of anomaly, which certainly seems to have some
value.

But if it doesn't have *enough* value, then we can go with the first
option I listed: do nothing.

> If anyone suggested tuning some aspect of the code, yet offered no
> evidence that it was ever important, it would get shot down. Why is this
> any different?

It's not.

> Allowing LOCK on views would significantly undermine admin structures
> where the only access to a table is via a view. This would allow people
> to lock objects they didn't previously have access to and seems likely
> to introduce more contention into applications as a result.

As long as we include appropriate privilege checks, this doesn't seem
like a major issue to me.

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