Re: Fix bug with accessing to temporary tables of other sessions - Mailing list pgsql-hackers

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)