Re: LOCK TABLE and DROP TABLE on temp tables of other sessions

On Fri, Feb 14, 2020 at 11:35 AM Michael Paquier <michael(at)paquier(dot)xyz>
wrote:

> On Thu, Feb 13, 2020 at 09:05:01PM +0530, Ashutosh Bapat wrote:
> > That looks odd. Other sessions are able to see temporary tables of a
> given
> > session because they are stored in the same catalog which is accessible
> to
> > all the sessions. But ideally, a temporary table should be visible only
> to
> > the session which created it (GTT is an exception). So LOCK and DROP
> table
> > should not succeed.
>
> One thing that we need to consider is if there are applications which
> take advantage of LOCK allowed on temp relations from other backends
> or not. One downside is that if one backend takes a lock on a temp
> table from a different session, then this other session would not
> completely shut down (still report the shutdown to the client),
> and would remain blocked during the temp schema cleanup until the
> transaction of the session locking the temp relation commits. This
> blocks access to one connection slot, still we are talking about an
> operation where the owner of the temp schema wants to do the lock.
>

That might be disastrous if happens by accident eating up most of the
available connection slots.

Whatever the user wants to achieve using LOCK [temp] TABLE of other
session, I guess can be achieved by other means or can be shown to have
disastrous effect. So that kind of usage pattern would better be forced to
change.

>
> > Thinking from a different perspective, DROP TABLE being able to drop a
> > temporary table seems a good tool in case a temporary table is left
> behind
> > by a finished session. But that doesn't seem like a good reason to have
> it
> > and I don't see much use of LOCK TABLE there.
>
> Yep. Robert had actually this argument with DROP SCHEMA pg_temp not
> so long ago with me.
>
>
DROP SCHEMA might be better for mass cleanup. That actually makes DROP
[other session temp] TABLE useless.

--
--
Best Wishes,
Ashutosh Bapat