Re: [Proposal] Global temporary tables - =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?=

public inbox for [email protected]  
help / color / mirror / Atom feed

From: =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= <[email protected]>
To: tushar <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: Prabhat Sahu <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: =?UTF-8?B?6JSh5p2+6ZyyKOWtkOWYiSk=?= <[email protected]>
Cc: =?UTF-8?B?Q2FpLCBMZQ==?= <[email protected]>
Cc: =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?= <[email protected]>
Subject: Re: [Proposal] Global temporary tables
Date: Wed, 26 Feb 2020 23:54:22 +0800
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>
	<CAFj8pRAcBb0TpfcQbrkfY4ymgqonqsW8=08Hj5ckwEQBAuf9Fw@mail.gmail.com>
	<[email protected]>



> 2020年2月25日 下午11:31，tushar <[email protected]> 写道：
> 
> Hi,
> 
> I have created two  global temporary tables like this -
> 
> Case 1- 
> postgres=# create global  temp table foo(n int) with (on_commit_delete_rows='true');
> CREATE TABLE
> 
> Case 2- 
> postgres=# create global  temp table bar1(n int) on commit delete rows;
> CREATE TABLE
> 
> 
> but   if i try to do the same having only 'temp' keyword , Case 2 is working fine but getting this error  for case 1 -
> 
> postgres=# create   temp table foo1(n int) with (on_commit_delete_rows='true');
> ERROR:  regular table cannot specifie on_commit_delete_rows
> postgres=# 
> 
> postgres=#  create   temp table bar1(n int) on commit delete rows;
> CREATE TABLE
> 
> i think this error message need to be more clear .
Also fixed in global_temporary_table_v14-pg13.patch

Wenjing



> 
> regards,
> tushar 
> 
> On 2/25/20 7:19 PM, Pavel Stehule wrote/:
>> 
>> 
>> út 25. 2. 2020 v 14:36 odesílatel Prabhat Sahu <[email protected] <mailto:[email protected]>> napsal:
>> Hi All,
>> 
>> Please check the below findings on GTT.
>> -- Scenario 1:
>> Under "information_schema", We are not allowed to create "temporary table", whereas we can CREATE/DROP "Global Temporary Table", is it expected ?
>> 
>> It is ok for me. temporary tables should be created only in proprietary schema. For GTT there is not risk of collision, so it can be created in any schema where are necessary access rights.
>> 
>> Pavel
>> 
>> 
>> postgres=# create temporary table information_schema.temp1(c1 int);
>> ERROR:  cannot create temporary relation in non-temporary schema
>> LINE 1: create temporary table information_schema.temp1(c1 int);
>>                                ^
>> 
>> postgres=# create global temporary table information_schema.temp1(c1 int);
>> CREATE TABLE
>> 
>> postgres=# drop table information_schema.temp1 ;
>> DROP TABLE
>> 
>> -- 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
>> 
>> Thanks,
>> Prabhat Sahu
>> 
>> On Tue, Feb 25, 2020 at 2:25 PM 曾文旌(义从) <[email protected] <mailto:[email protected]>> wrote:
>> 
>> 
>>> 2020年2月24日 下午5:44，Prabhat Sahu <[email protected] <mailto:[email protected]>> 写道：
>>> 
>>> On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从) <[email protected] <mailto:[email protected]>> wrote:
>>> Hi,
>>> I have started testing the "Global temporary table" feature,
>>> That's great, I see hope.
>>> from "gtt_v11-pg13.patch". Below is my findings:
>>> 
>>> -- session 1:
>>> postgres=# create global temporary table gtt1(a int);
>>> CREATE TABLE
>>> 
>>> -- seeeion 2:
>>> postgres=# truncate gtt1 ;
>>> ERROR:  could not open file "base/13585/t3_16384": No such file or directory
>>> 
>>> is it expected?
>>> 
>>> Oh ,this is a bug, I fixed it.
>>> Thanks for the patch.
>>> I have verified the same, Now the issue is resolved with v12 patch.
>>> 
>>> Kindly confirm the below scenario:
>>> 
>>> postgres=# create global temporary table gtt1 (c1 int unique);
>>> CREATE TABLE
>>> 
>>> postgres=# create global temporary table gtt2 (c1 int references gtt1(c1) );
>>> ERROR:  referenced relation "gtt1" is not a global temp table
>>> 
>>> postgres=# create table tab2 (c1 int references gtt1(c1) );
>>> ERROR:  referenced relation "gtt1" is not a global temp table
>>> 
>>> Thanks, 
>>> Prabhat Sahu
>> 
>> GTT supports foreign key constraints in global_temporary_table_v13-pg13.patch
>> 
>> 
>> Wenjing
>> 
>> 
>> 
>> 
>> 
>> -- 
>> With Regards,
>> Prabhat Kumar Sahu
>> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/;
> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/ <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