Re: Fix bug with accessing to temporary tables of other sessions

Hi Daniil,

On 7/29/25 11:35, Daniil Davydov wrote:
> I attach a v7 patch to this letter. No changes yet, just rebased on the newest
> commit in master branch.

A few days ago I reviewed one patch[1] that has a significant overlap
with this one. Perhaps they should be merged?

Here my first tests and comments:

== session 1 ==

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1
postgres=# \d tmp
Table "pg_temp_75.tmp"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
val | integer | | |

== session 2 ==

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

-- fixed: previously accessed the table but returning 0 rows
postgres=# SELECT * FROM pg_temp_75.tmp;
ERROR: could not access temporary relations of other sessions
LINE 1: SELECT * FROM pg_temp_75.tmp;
^
-- fixed: previously returning DELETE 0
postgres=# DELETE FROM pg_temp_75.tmp;
ERROR: could not access temporary relations of other sessions
LINE 1: DELETE FROM pg_temp_75.tmp;
^
postgres=# TRUNCATE TABLE pg_temp_75.tmp;
ERROR: could not access temporary relations of other sessions

-- fixed: previously returning UPDATE 0
postgres=# UPDATE pg_temp_75.tmp SET val = NULL;
ERROR: could not access temporary relations of other sessions
LINE 1: UPDATE pg_temp_75.tmp SET val = NULL;
^
-- error message changed: previously "ERROR: cannot access temporary
tables of other sessions"
postgres=# INSERT INTO pg_temp_75.tmp VALUES (73);
ERROR: could not access temporary relations of other sessions
LINE 1: INSERT INTO pg_temp_75.tmp VALUES (73);
^
-- fixed: previously returning COPY 0
postgres=# COPY pg_temp_75.tmp TO '/tmp/foo';
ERROR: could not access temporary relations of other sessions

-- error message changed. previously "ERROR: cannot alter temporary
tables of other sessions"
postgres=# ALTER TABLE pg_temp_75.tmp ADD COLUMN foo int;
ERROR: could not access temporary relations of other sessions

-- fixed: previously[2] it was possible to rename the temp table.
postgres=# ALTER TABLE pg_temp_75.tmp RENAME TO bar;
ERROR: could not access temporary relations of other sessions

-- fixed: previously[3] it was possible to LOCK the temp table.
postgres=# BEGIN;
BEGIN
postgres=*# LOCK TABLE pg_temp_75.tmp IN ACCESS EXCLUSIVE MODE;
ERROR: could not access temporary relations of other sessions

DROP TABLE still works, but I guess it is the main motivation of
RVR_OTHER_TEMP_OK :)

Thanks for the patch. It's a great improvement!

Best regards, Jim

[1]
https://www.postgresql.org/message-id/flat/2736425.1758475979%40sss.pgh.pa.us
[2] ALTER TABLE ... RENAME TO tests in PostgreSQL 14.19:

== session 1 ==
psql (14.19 (Debian 14.19-1.pgdg13+1))
Geben Sie »help« für Hilfe ein.

postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1
postgres=# \d tmp
Tabelle »pg_temp_4.tmp«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------+---------+--------------+---------------+-------------
val | integer | | |

== session 2 ==
psql (14.19 (Debian 14.19-1.pgdg13+1))
Geben Sie »help« für Hilfe ein.

postgres=# ALTER TABLE pg_temp_4.tmp RENAME TO foo;
ALTER TABLE

== session 1 ==

postgres=# \d tmp
Keine Relation namens »tmp« gefunden
postgres=# \d foo
Tabelle »pg_temp_4.foo«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------+---------+--------------+---------------+-------------
val | integer | | |

[3] LOCK TABLE tests in PostgreSQL 14.19
== session 2 ==
postgres=# BEGIN;
BEGIN
postgres=*# LOCK TABLE pg_temp_4.foo IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
postgres=*#

== session 1 ==
-- * owner of the temp table
postgres=# SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'pg_temp_4.foo'::regclass::oid;
locktype | relation | mode | granted | pid
----------+----------+---------------------+---------+--------
relation | foo | AccessExclusiveLock | t | 277699
(1 Zeile)