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").