Re: [Proposal] Global temporary tables - Mailing list pgsql-hackers

postgres=# CREATE LOCAL TEMPORARY TABLE gtt1(c1 serial PRIMARY KEY, c2 VARCHAR (50) UNIQUE NOT NULL) ON COMMIT DELETE ROWS;

CREATE TABLE

postgres=# CREATE LOCAL TEMPORARY TABLE gtt2(c1 integer NOT NULL, c2 integer NOT NULL,

postgres(# PRIMARY KEY (c1, c2),

postgres(# FOREIGN KEY (c1) REFERENCES gtt1 (c1)) ON COMMIT PRESERVE ROWS;

ERROR:  unsupported ON COMMIT and foreign key combination

DETAIL:  Table "gtt2" references "gtt1", but they do not have the same ON COMMIT setting.

postgres=# CREATE LOCAL TEMPORARY TABLE gtt3(c1 serial PRIMARY KEY, c2 VARCHAR (50) UNIQUE NOT NULL) ON COMMIT PRESERVE ROWS;

CREATE TABLE

postgres=# 

postgres=# CREATE LOCAL TEMPORARY TABLE gtt4(c1 integer NOT NULL, c2 integer NOT NULL,

postgres(# PRIMARY KEY (c1, c2),

postgres(# FOREIGN KEY (c1) REFERENCES gtt3 (c1)) ON COMMIT DELETE ROWS;

CREATE TABLE

The same behavior applies to the local temp table.

I think, Cause of the problem is temp table with on commit delete rows is not good for reference tables.

So, it the error message ”cannot reference an on commit delete rows temporary table.“ ?

Below are the 4 combinations of GTT/"local temp table" reference.
1. "ON COMMIT PRESERVE ROWS" can references to "ON COMMIT PRESERVE ROWS"
2. "ON COMMIT DELETE ROWS"   can references to "ON COMMIT PRESERVE ROWS"
3. "ON COMMIT DELETE ROWS"   can references to "ON COMMIT DELETE ROWS"
But
4. "ON COMMIT PRESERVE ROWS" fails to reference "ON COMMIT DELETE ROWS"