Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j6cBC-0002NR-Lz for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Feb 2020 15:31:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j6cBB-0003oT-B4 for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Feb 2020 15:31:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j6cBA-0003nh-L7 for pgsql-hackers@lists.postgresql.org; Tue, 25 Feb 2020 15:31:25 +0000 Received: from mail-pl1-x643.google.com ([2607:f8b0:4864:20::643]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j6cB4-00048r-Af for pgsql-hackers@postgresql.org; Tue, 25 Feb 2020 15:31:23 +0000 Received: by mail-pl1-x643.google.com with SMTP id y8so5616375pll.13 for ; Tue, 25 Feb 2020 07:31:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=jmp97g7o89vqIxqm82DJQ6+iLDiACUCwyQUeAGWsoM8=; b=wQP5exHkVrXQ/4alkS7wDZZWtqJmN3ZSMpmA5qHlqq6Yqv84JSuE7ipW33opLOMZl8 ghhkWhRo0cW4+qWvRZjNlpxofo988oAGNDqeN+SvIa75/jUN/slp75CF+Atyn7Sqac2N kxq2FMZo2AHI41FxdahLoIKIDl0ah6/+x8TwW2daBJ2t8w5qyS6ppiuGaetb1FQNFT+T V2p0gN4Kp85tp5Z9FC9QpIsfn/pIT9LLibpROnBO6tt1EZc189iYkMo1nrud1CJyLAgc 5oZ0R0doTDVVBQuBriUxtwYVjtT8W0jM12Aslaz6UWxWZLgRltq6LYzEZQibWuGoTP6R cPeQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=jmp97g7o89vqIxqm82DJQ6+iLDiACUCwyQUeAGWsoM8=; b=aOGub+HbWMJ+2q9yvLPKw+dXowyC8m4yBTWcR+usGlZceloXMCa/uUkE1rRY/fuW9G rtu52DR2bK3CshlLQWsKNqRoGV431YDnyWg1yZfIL6fmv5fKAqtT/anYaJ/Y7+96YR63 +wuq0YpnX2xfASMd+k0RhdOxX0Gm1tvq3kUWcy5EncYv+cSt52aPFmX9NyTC+cR9k15O 2NKhF0WIfIti17PYmlZlgnZyNnglDHtTkII2XVXMYWm4ALG3XymHlD2mdvMJYSnrzvUu IEVSQk5XZYtkb1JzqnKG3sBZ9b+mvpYigEIPNWJBrfzoRJB12pnvecGX35fKs8xxXhI9 KJ/Q== X-Gm-Message-State: APjAAAU/jwBosD0YF6P6gBQIydJjJmluUo+F7VyIr9fPY3hNCRBfDX8A Qg6R9eeMNLYmqUiXx2bbKiYZnrfV8Ze33nXOqXTpb9ydnekqQlZUsNYGUpnLRKsdfNYoe/AZ+l2 xMWH5syiykPP1E0xR2jbmApCCuB/JEHaiimDJh+lF+U+KrVbwaW7F41hOZHRYyAVTpaHOH08xZp wTSaN7/KOcgY0/8s8MtN7F83yedrFgzQ== X-Google-Smtp-Source: APXvYqwaorK2y6ERNVGfBVu8wfvzmJTDP1EbpITEjTE6OkJHeE1taUMF2dZLSMBwtBsUBWPGGZWYDg== X-Received: by 2002:a17:902:bd43:: with SMTP id b3mr52309387plx.230.1582644676960; Tue, 25 Feb 2020 07:31:16 -0800 (PST) Received: from localhost.localdomain ([114.143.109.178]) by smtp.gmail.com with ESMTPSA id a10sm16801547pgk.71.2020.02.25.07.31.12 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 25 Feb 2020 07:31:16 -0800 (PST) Subject: Re: [Proposal] Global temporary tables To: Pavel Stehule , Prabhat Sahu Cc: =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= , Robert Haas , Tomas Vondra , Konstantin Knizhnik , PostgreSQL Hackers , =?UTF-8?B?6JSh5p2+6Zyy?= =?UTF-8?B?KOWtkOWYiSk=?= , "Cai, Le" , =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?= References: <24099f4e-742c-e92b-729c-77bac1ca80d4@postgrespro.ru> <8517A6EA-17A6-4160-8987-B01073A0ECAF@alibaba-inc.com> <8BC7841E-3B32-4518-9586-D9ED571F4922@alibaba-inc.com> <35A2DF75-05A8-4F8D-9D43-A930E2ABDFDD@alibaba-inc.com> <82851B35-E2FB-4A5D-AF35-A2B5023DB9F2@alibaba-inc.com> From: tushar Message-ID: <2d56b893-7547-9c76-657c-32257d2f4e1f@enterprisedb.com> Date: Tue, 25 Feb 2020 21:01:09 +0530 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A59C13FF014475FA800614E4" Content-Language: en-US X-CLOUD-SEC-AV-Info: edb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A59C13FF014475FA800614E4 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi, I have created two  global temporary tables like this - Case 1- postgres=# create global  temp table foo(n int) *with (on_c*ommit_delete_rows='true'); CREATE TABLE Case 2- postgres=# create global  temp table bar1(n int) *on c*ommit 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 . regards, tushar On 2/25/20 7:19 PM, Pavel Stehule wrote/: > > > út 25. 2. 2020 v 14:36 odesílatel Prabhat Sahu > > > 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 曾文旌(义从) > > > wrote: > > > >> 2020年2月24日 下午5:44，Prabhat Sahu >> > > 写道： >> >> On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从) >> > > 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 > > -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company --------------A59C13FF014475FA800614E4 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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 .

regards,
tushar

On 2/25/20 7:19 PM, Pavel Stehule wrote/:


út 25. 2. 2020 v 14:36 odesílatel Prabhat Sahu <prabhat.sahu@enterprisedb.com> 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 曾文旌(义从) <wenjing.zwj@alibaba-inc.com> wrote:


2020年2月24日 下午5:44，Prabhat Sahu <prabhat.sahu@enterprisedb.com> 写道：

On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从) <wenjing.zwj@alibaba-inc.com> 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


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