Horton

Re: [Proposal] Global temporary tables

Prabhat Sahu <prabhat.sahu@enterprisedb.com>

View thread
From: Prabhat Sahu <prabhat.sahu@enterprisedb.com>
To: 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
Cc: tushar <tushar.ahuja@enterprisedb.com>, Pavel Stehule <pavel.stehule@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 蔡松露(子嘉) <zijia@taobao.com>, "Cai, Le" <le.cai@alibaba-inc.com>, 萧少聪(铁庵) <shaocong.xsc@alibaba-inc.com>
Date: 2020-03-13T14:16:32Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. PageAddItemExtended(): Add LP_UNUSED assertion.

  2. Remove temporary files after backend crash

  3. Fix comment in indexing.c

  4. Fix failure to ignore leftover temp tables after a server crash.

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