Re: [Proposal] Global temporary tables

2020年3月2日 下午10:47，tushar <tushar.ahuja@enterprisedb.com> 写道：

On 2/27/20 9:43 AM, 曾文旌(义从) wrote:

-- Scenario 2:
Here I am getting the same error message in both the below cases.
We may add a "global" keyword with GTT related error message.

postgres=# create global temporary table gtt1 (c1 int unique);
CREATE TABLE
postgres=# create temporary table tmp1 (c1 int unique);
CREATE TABLE

postgres=# create temporary table tmp2 (c1 int references gtt1(c1) );
ERROR:  constraints on temporary tables may reference only temporary tables

postgres=# create global temporary table gtt2 (c1 int references tmp1(c1) );
ERROR:  constraints on temporary tables may reference only temporary tables

Fixed in global_temporary_table_v15-pg13.patch

Thanks Wenjing.

This below scenario is not working  i.e even 'on_commit_delete_rows' is true then after commit -  rows are NOT removing

postgres=#  create global  temp table foo1(n int) with (on_commit_delete_rows='true');
CREATE TABLE
postgres=#
postgres=# begin;
BEGIN
postgres=*# insert into foo1 values (9);
INSERT 0 1
postgres=*# insert into foo1 values (9);
INSERT 0 1
postgres=*# select * from foo1;
 n
---
 9
 9
(2 rows)

postgres=*# commit;
COMMIT
postgres=# select * from foo1;   -- after commit -there should be 0 row as on_commit_delete_rows is 'true'
 n
---
 9
 9
(2 rows)

postgres=# \d+ foo1
                                   Table "public.foo1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 n      | integer |           |          |         | plain   |              |
Access method: heap
Options: on_commit_delete_rows=true

postgres=#

but if user - create table this way then it is working as expected

postgres=#  create global  temp table foo2(n int) on commit delete rows;
CREATE TABLE
postgres=# begin; insert into foo2 values (9); insert into foo2 values (9); commit; select * from foo2;
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
 n
---
(0 rows)

postgres=#

i guess , problem is something with this syntax - create global  temp table foo1(n int) with (on_commit_delete_rows='true'); 

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company