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.“ ?
Hi Wenjing,
Please check the below combination of GTT with Primary and Foreign key relations, with the ERROR message.
Case1:
postgres=# CREATE GLOBAL TEMPORARY TABLE gtt1(c1 serial PRIMARY KEY, c2 VARCHAR (50) UNIQUE NOT NULL) ON COMMIT DELETE ROWS;
CREATE TABLE
postgres=# CREATE GLOBAL TEMPORARY TABLE gtt2(c1 integer NOT NULL, c2 integer NOT NULL,
PRIMARY KEY (c1, c2),
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.
Case2:
postgres=# CREATE GLOBAL TEMPORARY TABLE gtt1(c1 serial PRIMARY KEY, c2 VARCHAR (50) UNIQUE NOT NULL) ON COMMIT PRESERVE ROWS;
CREATE TABLE
postgres=# CREATE GLOBAL TEMPORARY TABLE gtt2(c1 integer NOT NULL, c2 integer NOT NULL,
PRIMARY KEY (c1, c2),
FOREIGN KEY (c1) REFERENCES gtt1 (c1)) ON COMMIT DELETE ROWS;
CREATE TABLE
In "case2" although both the primary table and foreign key GTT do not have the same ON COMMIT setting, still we are able to create the PK-FK relations with GTT.
So I hope the detail message(DETAIL: Table "gtt2" references "gtt1", but they do not have the same ON COMMIT setting.) in "Case1" should be more clear(something like "wrong combination of ON COMMIT setting").
--