public inbox for [email protected]  

help / color / mirror / Atom feed

From: tushar <[email protected]>
To: 曾文旌(义从) <[email protected]>
To: Prabhat Sahu <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: 蔡松露(子嘉) <[email protected]>
Cc: Cai, Le <[email protected]>
Cc: 萧少聪(铁庵) <[email protected]>
Subject: Re: [Proposal] Global temporary tables
Date: Mon, 2 Mar 2020 20:17:19 +0530
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<CA+TgmobtnmTguphZ_7536yhzENzCSDPEkJnha-29a4Q7hAwEWg@mail.gmail.com>
	<[email protected]>
	<CAFj8pRBSFwhj+iccFF50WdrruAXbMsvqTMTfCVu9x2LPcvnd2w@mail.gmail.com>
	<CAFj8pRBNssuJoYH6d51-yqOfV2_nB+aK-Mj7maLPQH+O8cm-TA@mail.gmail.com>
	<[email protected]>
	<CAFj8pRCz52CbxnNmO=NK7cBA=9o=rdWVEBJRKOeVJ1FwzqzLRw@mail.gmail.com>
	<[email protected]>
	<CAFj8pRADaF0eAg4-3RZhLxz_+zaKLQe5gFPGoqRdSFByvhmxEw@mail.gmail.com>
	<[email protected]>
	<CANEvxPqkJ8ubPcwZLP9m_4qdNmAo+LrJTt95-B9RbWhbZ-8Nsw@mail.gmail.com>
	<[email protected]>
	<CANEvxPrEAtmWN6z8T3ed81V5WZBxfrQ2LmHO1uOEOeUBh83Z-Q@mail.gmail.com>
	<[email protected]>

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

view thread (492+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [Proposal] Global temporary tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox