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 1j8mM2-0001gZ-N2 for pgsql-hackers@arkaria.postgresql.org; Mon, 02 Mar 2020 14:47:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j8mM1-0001Zd-AR for pgsql-hackers@arkaria.postgresql.org; Mon, 02 Mar 2020 14:47:33 +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 1j8mM0-0001ZV-QE for pgsql-hackers@lists.postgresql.org; Mon, 02 Mar 2020 14:47:33 +0000 Received: from mail-pj1-x1044.google.com ([2607:f8b0:4864:20::1044]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j8mLw-0006Bv-Cs for pgsql-hackers@postgresql.org; Mon, 02 Mar 2020 14:47:31 +0000 Received: by mail-pj1-x1044.google.com with SMTP id l8so1012031pjy.1 for ; Mon, 02 Mar 2020 06:47:28 -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=hPBkRz3/LhXSd/8RJ5wspeVpE1BIlAul8i0YT0Dw7f8=; b=h/+zaG0EJmlEAsJAaU1fBFUEdtXsm/zWFHKLVsz45dhu90T2d2ORqMa7+5tkk+dihw l2ocOadZtCj1bsxntaBjEEVd2CuNaSRB4NMf4KjyqqyIhMYJk2aFHNwOPQTT8XesZBCQ iIrpTNlHQVCMXkivWZ4lk8eJu7x5zDZDQp0/o7A7rnUczmZCUVurzzK03OMOnnwoDs4G 28i0e/Ol4v7DcRUJ6BmOLKiChjlrdcD5SheeOjfgqp1nFGIG5m6Z6D7somOpUCvB+N5I FC2P8YvCNKDPkAqQhpYU6bghyvcm0b1Kgtwg6RrnqXVoruHhl3gUI76wbfV8TUYulC1Z T9xQ== 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=hPBkRz3/LhXSd/8RJ5wspeVpE1BIlAul8i0YT0Dw7f8=; b=iJmFyDvgp1J3XUTpPxc39RR2ZCtBJ/7wpTvzmfmY5LgoyuvgO0aVBsXsQQv/seP+kr CMvy42fyY9DBxusL8OkUF2+2j/FOLma1mkjuBdEX5XU6c4JTvKJ7FClwWX6xKeDR8o5j cc7M8wZIP41EMA/Eg+yXmpLo/FjNVJxv0iplIa3Ig5+fg9z5B4iMY7h5Aog8OkVyTMoL 00KodkSwbnV3HWhscMibssLuNGu2aqhMLgpq4AB7Lon2Uyec98la5szhT5xA4L4zx+6B a/HY0h1A7AdudN/hABYxT4/dWkAZ3fhP+T9eYIJqcNRvm/bJgwj/Wa2x4Qs4c1R8ryMi Xxeg== X-Gm-Message-State: ANhLgQ1tlciEWidijdLVYROPUKgpYEF/m9TP7YExdzky3PUgTzLZkWlL NDzRfeOvw7VjYOAD18RlbDG5LNTxeYXf2qDZ0PYQn8b4rA8fbZfweauWHLiIToHhEKBuB6VBBhL pRLlXEAbeii3ve1DMD1v9G5SqAnGNX/CNtZkcecIfz2kHNWtNQxYFc7+bv5QirfgGTh9ORLPYX1 6y/8awNIyiJvvKkhTo1j+yD4ilYRy83A== X-Google-Smtp-Source: ADFU+vtpmLLZ84oGywiCTUWLiux8uoUhvt73WMPosaYsBcmM7M01wmazLU8Fw8laYjdJ3+9XcCvtYA== X-Received: by 2002:a17:90b:1953:: with SMTP id nk19mr72352pjb.98.1583160447008; Mon, 02 Mar 2020 06:47:27 -0800 (PST) Received: from localhost.localdomain ([114.143.109.178]) by smtp.gmail.com with ESMTPSA id 9sm17514808pge.65.2020.03.02.06.47.23 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 02 Mar 2020 06:47:26 -0800 (PST) Subject: Re: [Proposal] Global temporary tables To: =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= , Prabhat Sahu 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> <77320A29-DC39-4EFE-A5E2-0A00F6A316E8@alibaba-inc.com> From: tushar Message-ID: Date: Mon, 2 Mar 2020 20:17:19 +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: <77320A29-DC39-4EFE-A5E2-0A00F6A316E8@alibaba-inc.com> Content-Type: multipart/alternative; boundary="------------DC924BCE868A3AE63137E908" 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. --------------DC924BCE868A3AE63137E908 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------DC924BCE868A3AE63137E908 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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
--------------DC924BCE868A3AE63137E908--