Rethinking LOCK TABLE's behavior on views

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Subject: Rethinking LOCK TABLE's behavior on views
Date: 2020-11-07 16:57:20
Message-ID: 1718681.1604768240@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The problems discussed in bug #16703 [1] show that pg_dump needs a
version of LOCK TABLE that behaves differently for views than
what we have now. Since v11, LOCK TABLE on a view recurses to all
tables and views named in the view, and it does so using the view
owner's permissions, meaning that a view that would have permissions
failures if executed will also have permissions failures when locked.
That's probably fine for ordinary usage, but it's disastrous for
pg_dump --- even a superuser can't lock such a view.

Moreover, pg_dump doesn't really need the recursive behavior. It just
needs the view's definition to hold still; and in any case, a typical
pg_dump run would have independently acquired locks on all the other
relations anyway. The recursion is buying us nothing, except perhaps
an increased risk of deadlocks against concurrent DDL operations.
(I'm not quite sure if that's significant, given that pg_dump pays
no attention to the order in which it locks things. But it sure as
heck isn't *decreasing* the risk; and it's a behavior that we could
not hope to improve with more smarts about pg_dump's lock ordering.)

So we need a way to turn that off. What I proposed in that thread
was

> (1) Make pg_dump use LOCK TABLE ONLY, not LOCK TABLE.
> (2) Make LOCK TABLE ONLY on a view not recurse to the view's dependencies.

which would each be quite trivial to do. An objection to this approach
is that ONLY typically controls recursion to a table's inheritance or
partitioning children, which is a different animal from recursion to
a view's dependencies. That argument would lead to wanting some other
syntax to control this. I do not find that argument compelling enough
to justify making pg_dump deal with two different commands depending
on the relation's relkind, but it is a plausible concern.

Closely related to this is whether pg_dump ought to be using ONLY for
locking regular tables too. I tend to think that it should be, again
on the grounds that any child tables we may be interested in will get
locked separately, so that we're not doing anything by recursing except
expending extra cycles and perhaps increasing the chance of a deadlock.

A completely different approach we could consider is to weaken the
permissions requirements for LOCK on a view, say "allow it if either
the calling user or the view owner has the needed permission". This
seems generally pretty messy and so I don't much like it, but we
should consider as many solutions as we can think of.

Thoughts?

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/16703-e348f58aab3cf6cc%40postgresql.org

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2020-11-07 17:33:27 RE: pgbench: option delaying queries till connections establishment?
Previous Message Andrey Borodin 2020-11-07 15:42:31 Re: Yet another fast GiST build