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 1j6z1D-0002Lw-7P for pgsql-hackers@arkaria.postgresql.org; Wed, 26 Feb 2020 15:54:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j6z1B-0000cv-VN for pgsql-hackers@arkaria.postgresql.org; Wed, 26 Feb 2020 15:54:37 +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 1j6z1B-0000ck-EQ for pgsql-hackers@lists.postgresql.org; Wed, 26 Feb 2020 15:54:37 +0000 Received: from out0-141.mail.aliyun.com ([140.205.0.141]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j6z15-0007IG-Te for pgsql-hackers@postgresql.org; Wed, 26 Feb 2020 15:54:36 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=alibaba-inc.com; s=default; t=1582732465; h=From:Message-Id:Content-Type:Mime-Version:Subject:Date:To; bh=18pWidFiG5Uw7tCAfeC+1+CgkXnNKCcb1PFqYiZkg5E=; b=YsBJtDIsEgtfp9Jv9ICIjhK4MaAQenIRXb+9qco3716SHgI2UVsqepXOzI7sDGlY31tEyuGtasIo+wDBDDd+MWXTKdnt7Fu7RiZLQMvRvNBaCKp1bAc3Q1carDsB+aLeUCF7BZeY+S1rABiRVMZyw5Gc9J0QKpUIf//3j0Wkw5E= X-Alimail-AntiSpam: AC=PASS;BC=-1|-1;BR=01201311R201e4;CH=green;DM=||false|;DS=||;FP=0|-1|-1|-1|0|-1|-1|-1;HT=e02c03308;MF=wenjing.zwj@alibaba-inc.com;NM=1;PH=DS;RN=10;SR=0;TI=SMTPD_---.GsmHCJL_1582732462; Received: from 30.39.239.236(mailfrom:wenjing.zwj@alibaba-inc.com fp:SMTPD_---.GsmHCJL_1582732462) by smtp.aliyun-inc.com(127.0.0.1); Wed, 26 Feb 2020 23:54:22 +0800 From: "=?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?=" Message-Id: <1B2CF30A-57EB-4BA3-801C-278022A4DC45@alibaba-inc.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_FD2A6317-C2F6-4C2B-B48C-0D6EA78F69DF" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\)) Subject: Re: [Proposal] Global temporary tables Date: Wed, 26 Feb 2020 23:54:22 +0800 In-Reply-To: <2d56b893-7547-9c76-657c-32257d2f4e1f@enterprisedb.com> Cc: "Pavel Stehule" , "Prabhat Sahu" , "Robert Haas" , "Tomas Vondra" , "Konstantin Knizhnik" , "PostgreSQL Hackers" , "=?UTF-8?B?6JSh5p2+6ZyyKOWtkOWYiSk=?=" , "=?UTF-8?B?Q2FpLCBMZQ==?=" , "=?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?=" To: tushar 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> <2d56b893-7547-9c76-657c-32257d2f4e1f@enterprisedb.com> X-Mailer: Apple Mail (2.3608.60.0.2.5) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_FD2A6317-C2F6-4C2B-B48C-0D6EA78F69DF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > 2020=E5=B9=B42=E6=9C=8825=E6=97=A5 =E4=B8=8B=E5=8D=8811:31=EF=BC=8Ctusha= r =E5=86=99=E9=81=93=EF=BC=9A >=20 > Hi, >=20 > I have created two global temporary tables like this - >=20 > Case 1-=20 > postgres=3D# create global temp table foo(n int) with = (on_commit_delete_rows=3D'true'); > CREATE TABLE >=20 > Case 2-=20 > postgres=3D# create global temp table bar1(n int) on commit delete = rows; > CREATE TABLE >=20 >=20 > but if i try to do the same having only 'temp' keyword , Case 2 is = working fine but getting this error for case 1 - >=20 > postgres=3D# create temp table foo1(n int) with = (on_commit_delete_rows=3D'true'); > ERROR: regular table cannot specifie on_commit_delete_rows > postgres=3D#=20 >=20 > postgres=3D# create temp table bar1(n int) on commit delete rows; > CREATE TABLE >=20 > i think this error message need to be more clear . Also fixed in global_temporary_table_v14-pg13.patch Wenjing >=20 > regards, > tushar=20 >=20 > On 2/25/20 7:19 PM, Pavel Stehule wrote/: >>=20 >>=20 >> =C3=BAt 25. 2. 2020 v 14:36 odes=C3=ADlatel Prabhat Sahu = > = napsal: >> Hi All, >>=20 >> 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 ? >>=20 >> 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. >>=20 >> Pavel >>=20 >>=20 >> postgres=3D# 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); >> ^ >>=20 >> postgres=3D# create global temporary table = information_schema.temp1(c1 int); >> CREATE TABLE >>=20 >> postgres=3D# drop table information_schema.temp1 ; >> DROP TABLE >>=20 >> -- 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. >>=20 >> postgres=3D# create global temporary table gtt1 (c1 int unique); >> CREATE TABLE >> postgres=3D# create temporary table tmp1 (c1 int unique); >> CREATE TABLE >>=20 >> postgres=3D# create temporary table tmp2 (c1 int references gtt1(c1) = ); >> ERROR: constraints on temporary tables may reference only temporary = tables >>=20 >> postgres=3D# create global temporary table gtt2 (c1 int references = tmp1(c1) ); >> ERROR: constraints on temporary tables may reference only temporary = tables >>=20 >> Thanks, >> Prabhat Sahu >>=20 >> On Tue, Feb 25, 2020 at 2:25 PM =E6=9B=BE=E6=96=87=E6=97=8C(=E4=B9=89=E4= =BB=8E) > wrote: >>=20 >>=20 >>> 2020=E5=B9=B42=E6=9C=8824=E6=97=A5 =E4=B8=8B=E5=8D=885:44=EF=BC=8CPrab= hat Sahu > =E5=86=99=E9=81=93=EF=BC=9A >>>=20 >>> On Fri, Feb 21, 2020 at 9:10 PM =E6=9B=BE=E6=96=87=E6=97=8C(=E4=B9=89=E4= =BB=8E) > 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: >>>=20 >>> -- session 1: >>> postgres=3D# create global temporary table gtt1(a int); >>> CREATE TABLE >>>=20 >>> -- seeeion 2: >>> postgres=3D# truncate gtt1 ; >>> ERROR: could not open file "base/13585/t3_16384": No such file or = directory >>>=20 >>> is it expected? >>>=20 >>> 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. >>>=20 >>> Kindly confirm the below scenario: >>>=20 >>> postgres=3D# create global temporary table gtt1 (c1 int unique); >>> CREATE TABLE >>>=20 >>> postgres=3D# create global temporary table gtt2 (c1 int references = gtt1(c1) ); >>> ERROR: referenced relation "gtt1" is not a global temp table >>>=20 >>> postgres=3D# create table tab2 (c1 int references gtt1(c1) ); >>> ERROR: referenced relation "gtt1" is not a global temp table >>>=20 >>> Thanks,=20 >>> Prabhat Sahu >>=20 >> GTT supports foreign key constraints in = global_temporary_table_v13-pg13.patch >>=20 >>=20 >> Wenjing >>=20 >>=20 >>=20 >>=20 >>=20 >> --=20 >> With Regards, >> Prabhat Kumar Sahu >> EnterpriseDB: http://www.enterprisedb.com = >=20 > --=20 > regards,tushar > EnterpriseDB https://www.enterprisedb.com/ = > The Enterprise PostgreSQL Company --Apple-Mail=_FD2A6317-C2F6-4C2B-B48C-0D6EA78F69DF Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

2020=E5=B9=B42=E6=9C=8825=E6=97=A5 =E4=B8=8B=E5=8D=8811:31=EF=BC= =8Ctushar <tushar.ahuja@enterprisedb.com> =E5=86=99=E9=81=93=EF=BC=9A=

=20 =20
Hi,

I have created two  global = temporary tables like this -

Case 1-
postgres=3D# create global  temp table foo(n int) with (on_commit_delete_rows=3D'true');
CREATE TABLE

Case 2-
postgres=3D# 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=3D# create   temp = table foo1(n int) with (on_commit_delete_rows=3D'true');
ERROR:  regular table cannot specifie = on_commit_delete_rows
postgres=3D#

postgres=3D#  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/:


=C3=BAt 25. 2. 2020 = v 14:36 odes=C3=ADlatel 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=3D# 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=3D# create global temporary table information_schema.temp1(c1 int);
CREATE TABLE

postgres=3D# 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=3D# create global temporary table gtt1 (c1 = int unique);
CREATE TABLE
postgres=3D# create temporary table tmp1 (c1 int unique);
CREATE TABLE

postgres=3D# create temporary table tmp2 (c1 int references gtt1(c1) );
ERROR:  constraints on temporary tables may = reference only temporary tables

postgres=3D# 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 =E6=9B=BE=E6=96=87=E6=97=8C(=E4=B9=89=E4=BB=8E) = <wenjing.zwj@alibaba-inc.com> wrote:


2020=E5=B9=B42=E6=9C=8824=E6=97=A5 = =E4=B8=8B=E5=8D=885:44=EF=BC=8CPrabhat Sahu <prabhat.sahu@enterprisedb.com> =E5=86=99=E9=81=93=EF=BC=9A

On Fri, Feb 21, 2020 at 9:10 PM = =E6=9B=BE=E6=96=87=E6=97=8C(=E4=B9=89=E4=BB=8E) <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=3D# create global temporary table gtt1(a int);
CREATE TABLE

-- seeeion 2:
postgres=3D# 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=3D# create global temporary table gtt1 (c1 int unique);
CREATE TABLE

postgres=3D# create global temporary table gtt2 (c1 int references gtt1(c1) );
ERROR:  referenced relation "gtt1" is = not a global temp table

postgres=3D# 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


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

= --Apple-Mail=_FD2A6317-C2F6-4C2B-B48C-0D6EA78F69DF--