LOCK TABLE and DROP TABLE on temp tables of other sessions

On Thu, Feb 13, 2020 at 6:40 PM Fujii Masao <masao.fujii@oss.nttdata.com>
wrote:

Hi,

TRUNCATE command on the temporary tables of other sessions fails
with the following error. This behavior looks expected to me.

ERROR: cannot truncate temporary tables of other sessions

However I found that LOCK TABLE and DROP TABLE commands on
the temporary tables of other sessions are successfully processed,
if users (like superusers) have enough access privileges on them.
Is this a bug? ISTM that the similar check that TRUNCATE command
does needs to be added even in LOCK TABLE and DROP TABLE cases.

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.

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.

BTW, even SELECT has the same issue. Basically SELECT on
the temporary tables of other sessions fails with the following
error.

ERROR: cannot access temporary tables of other sessions

However if the temporary table is empty, SELECT doesn't reach
the above check, is successfully processed and the relation lock
is taken. This lock can prevent the backend process that created
the temporary table from exiting even when the client that
the backend is connecting to quits. Seems it's problematic.

Regards,

--
Fujii Masao
NTT DATA CORPORATION
Advanced Platform Technology Group
Research and Development Headquarters

--
--
Best Wishes,
Ashutosh Bapat

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.

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.
--
Michael

On Fri, Feb 14, 2020 at 11:35 AM Michael Paquier <michael@paquier.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

On Fri, Feb 14, 2020 at 05:59:34PM +0530, Ashutosh Bapat wrote:

On Fri, Feb 14, 2020 at 11:35 AM Michael Paquier <michael@paquier.xyz> wrote:

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.

Well, that would be an owner doing that.

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.

Anyway, don't take me wrong. I would be rather in favor of
restricting LOCK but that does not seem like something enough for a
backpatch. One recent example in this area I had to deal with is
REINDEX on temp tables. We have some assumptions which involve lock
upgrades (ShareUpdateExclusiveLock => AccessExclusiveLock between the
moment we take the relation lock using its RangeVar until the moment
the reindex is actually done), so being able to take a conflicting
lock on the temp relation could cause reindex to deadlock.
--
Michael