Problem without Transaction Around Creation

[chriszwickerergon]

Background: I'm teaching a course and I'm using helper scripts to allow students to write their queries in a file and to execute them in a docker container with image postgres:latest (16.0-bookworm at the time of writing).

I load PGTT via psqlrc.

I noticed two strange things:

• when I execute the following using docker exec <container-name> psql -U postgres -d <db-name> -c "$1", I get an error "ERROR: ON COMMIT can only be used on temporary tables"

  create /*global*/ temporary table glotemp_ex_1 (id varchar) on commit preserve rows;
  insert into glotemp_ex_1 values ('A');
  select * from glotemp_ex_1;

• when I surround the creation with a transaction, it works, but when I open another session, the table does not exist!

  begin; create /*global*/ temporary table glotemp_ex_1 (id varchar) on commit preserve rows; commit;
  insert into glotemp_ex_1 values ('A');
  select * from glotemp_ex_1;

It gets worse:

• when I set AUTOCOMMIT to "off" and do the same one by one, it works
• if I rollback after the creation of the GTT and then try to delete it, I still can't if it's in use, BUT
• if I exit and create a new session after rolling back, the table does not exist.

I'm a bit at a loss...

[darold]

Hi,

You should keep in mind that the creation of a global temporary table is a "maintenance" task, it is usually done by a DBA as a separate database schema change task before it is used. The table creation must be committed before being used by other sessions. You should not create it and use it "on the fly" like you are doing, for this purpose you have the standard PostgreSQL temporary tables.

[chriszwickerergon]

Hi Gilles,

Thank you for your quitck response! I understand your point, but these days, such setups are typically scripted, are they not?

In any case, I'm not sure why the second alternative (separate committed transaction) leaves the database in a state where the gtt does not exist for new sessions, shouldn't that work?

Best,
Chris

[darold]

No, the global temporary table creation is usually not scripted, this is a DDL run by the DBA or at database creation time.

When the GTT is created in a separated committed transaction it is immediately available for other transaction. In my tests I'm not able to reproduce what you are describing with latest release.

[darold]

Do you have the same behavior without using docker?