Re: Rethinking LOCK TABLE's behavior on views

On Mon, Nov 09, 2020 at 11:42:33AM -0300, Alvaro Herrera wrote:
> On 2020-Nov-07, Noah Misch wrote:
> > On Sat, Nov 07, 2020 at 11:57:20AM -0500, Tom Lane wrote:
> > > 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.
> >
> > This is the best of what you've listed by a strong margin, and I don't know of
> > better options you've not listed. +1 for it. Does it work for you?
>
> It does sound attractive from a user complexity perspective, even if it
> does sound messy form an implementation perspective.
>
> > I think
> > the mess arises from LOCK TABLE serving "get locks sufficient for $ACTIONS" as
> > a family of use cases. For views only, different $ACTIONS want different
> > behavior. $ACTIONS==SELECT wants today's behavior; pg_get_viewdef() wants
> > shallower recursion and caller permissions; DROP VIEW wants no recursion.
>
> Maybe we can tackle this problem directly, by adding a clause to LOCK
> TABLE to indicate a purpose for the lock that the server can use to
> determine the level of recursion. For example
> LOCK TABLE xyz IN <mode> FOR <purpose>
> where <purpose> can be READ, DROP, DEFINE.

Possible. Regrettably, we're not set up for it; running pg_get_viewdef() to
completion is today's way to determine what it will lock. Each of these modes
probably would have condensed copies of the operation they mimic, which I'd
find sadder than locking somewhat more than pg_dump needs (via today's "LOCK
TABLE viewname" behavior). Is it plausible to do without that duplication?