Re: [Proposal] Global temporary tables

> 2020年3月2日 下午10:47，tushar <tushar(dot)ahuja(at)enterprisedb(dot)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');
>
Thanks for review.

I fixed in global_temporary_table_v16-pg13.patch.

Wenjing

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