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 1j6ah7-0007ij-TV for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Feb 2020 13:56:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j6ah6-0005cH-IW for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Feb 2020 13:56:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j6ah6-0005cA-7p for pgsql-hackers@lists.postgresql.org; Tue, 25 Feb 2020 13:56:16 +0000 Received: from mail-pj1-x1044.google.com ([2607:f8b0:4864:20::1044]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j6ah3-00052l-55 for pgsql-hackers@postgresql.org; Tue, 25 Feb 2020 13:56:15 +0000 Received: by mail-pj1-x1044.google.com with SMTP id r67so1291210pjb.0 for ; Tue, 25 Feb 2020 05:56:12 -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=Xe5StYiz5Z+tEs0tlAfNcTjhkH1/RNHH9EqN15dnKSk=; b=NfQugfNo3hJF9837KFZGvvzdZGH3glTeot2VCQ+NoKH45NZX9X9BqG2U6M6o1jG0AB j4qqeGUhJoxwSlWWS1eTuElrnP78fr4uXeK5vZC/dYlKBKqf6bTL12rRUxx3SqvNCm7N yWoxYeiQTG8OSeGts18SYCp9OUWzjZ682/oCSp8wivi4agDRkEqN/hJ95US7nSeUJ+UB aIYHuw0HijPC4GYWGPWuQg+aDRE1PTaviUFEHYcF6BXDgoM3MEnDCm93Vdhfrb5ou1Mb FCtlCQMpwOJvsQNIiKwdcT5NxHms/ffJLXman/BFn8vAAyKgFQzLUpvVrhPL1VxJxioo qAEw== 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=Xe5StYiz5Z+tEs0tlAfNcTjhkH1/RNHH9EqN15dnKSk=; b=FtriwQKXuIcspgiiwHFk/OFEguqJLGUuSphbhWCfRG/cXAuv1OnO1bYCFWVowoVN9C DDd/UkwZUb96mG0IPy4io9+jqKaEN6NU5/r80hpCbTiF8wsSX/QrMEutnNt4M2IsXcdj QgQHroS+oGziqTNXK1KsqkohzOC9mHXn3jINe/R112EglsjJ3RcJrg9aAFikr4Qm9Hp1 yco/nx0zKDpL2dgWzbTDtYL86nz4d8n4D/tQl6oGZWfTK8qAdj3rz5PODglYYRS+OH0q qDi2HDoXsPCo5nvqIyM6fU07lHbA3Y8OC+hu59hmLuzCPNrHEKBo2WPBy50Z2VJPj5Nf lhXg== X-Gm-Message-State: APjAAAXgIABJDEdpubXlCYzBJYq2khCOTlP7rOjCeYAi74N4wLtGwjrJ 1IXnBy8p8DEtytLalUYA1WypjQRgE4q+HsPnrEgND+iJDkkxh1yVZK6A8qzZZVuDpQfAwWXSoUO jEO5vN9zw7kx5Me3pmK0EPZjvYyxsimzOF3WLwXBOyl/QzvNw4q2CejNCYzQoi84yha7eFBb7dK SlgeQbPewIKEuFcJ9YofVmvCnd65HhDQ== X-Google-Smtp-Source: APXvYqy4JG4YfOXaG02drBh3yUNNp4AF/MbdoqUoZKtIoW1nOQYtfODcPmk+CfbGPXgLDnWXOFjUig== X-Received: by 2002:a17:90a:c24c:: with SMTP id d12mr5426914pjx.113.1582638970488; Tue, 25 Feb 2020 05:56:10 -0800 (PST) Received: from localhost.localdomain ([114.143.109.178]) by smtp.gmail.com with ESMTPSA id q9sm14358965pgs.89.2020.02.25.05.56.06 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 25 Feb 2020 05:56:09 -0800 (PST) Subject: Re: [Proposal] Global temporary tables To: Prabhat Sahu , =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= Cc: Pavel Stehule , 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: Date: Tue, 25 Feb 2020 19:26:02 +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="------------F66C9B1CA7925CED69DF9CFC" 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. --------------F66C9B1CA7925CED69DF9CFC Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi , pg_upgrade  scenario is failing if database is containing  global temporary table ============================= centos@tushar-ldap-docker bin]$ ./psql postgres psql (13devel) Type "help" for help. postgres=# create global temporary table  t(n int); CREATE TABLE postgres=# \q =============================== run pg_upgrade - [centos@tushar-ldap-docker bin]$ ./pg_upgrade -d /tmp/t1/ -D /tmp/t2 -b . -B . Performing Consistency Checks ----------------------------- Checking cluster versions    ok Checking database user is the install user                   ok Checking database connection settings                       ok Checking for prepared transactions                             ok Checking for reg* data types in user tables                 ok -- -- If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade ------------------ Analyzing all rows in the new cluster                        ok Freezing all rows in the new cluster                          ok Deleting files from new pg_xact                                ok -- -- Restoring database schemas in the new cluster ok Copying user relation files   /tmp/t1/base/13585/16384 error while copying relation "public.t": could not open file "/tmp/t1/base/13585/16384": No such file or directory Failure, exiting regards, On 2/25/20 7:06 PM, Prabhat Sahu wrote: > 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 ? > > 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 --------------F66C9B1CA7925CED69DF9CFC Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
Hi ,

pg_upgrade  scenario is failing if database is containing  global temporary table

=============================
centos@tushar-ldap-docker bin]$ ./psql postgres
psql (13devel)
Type "help" for help.

postgres=# create global temporary table  t(n int);
CREATE TABLE
postgres=# \q
===============================

run pg_upgrade -

[centos@tushar-ldap-docker bin]$ ./pg_upgrade -d /tmp/t1/ -D /tmp/t2 -b . -B .
Performing Consistency Checks
-----------------------------
Checking cluster versions                                             ok
Checking database user is the install user                   ok
Checking database connection settings                       ok
Checking for prepared transactions                             ok
Checking for reg* data types in user tables                 ok
--
--
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                        ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                                ok
--
--
Restoring database schemas in the new cluster
                                                                                  ok
Copying user relation files
  /tmp/t1/base/13585/16384                                 
error while copying relation "public.t": could not open file "/tmp/t1/base/13585/16384": No such file or directory
Failure, exiting

regards,

On 2/25/20 7:06 PM, Prabhat Sahu wrote:
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 ?

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
--------------F66C9B1CA7925CED69DF9CFC--