Horton

Re: [Proposal] Global temporary tables

tushar <tushar.ahuja@enterprisedb.com>

View thread
From: tushar <tushar.ahuja@enterprisedb.com>
To: 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>, Prabhat Sahu <prabhat.sahu@enterprisedb.com>
Cc: 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-02T14:47:19Z
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.

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