Re: [Proposal] Global temporary tables

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

--

With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com