Re: [Proposal] Global temporary tables

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