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 1jDla2-0006L0-Vh for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Mar 2020 08:58: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 1jDla1-0008Rn-LT for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Mar 2020 08:58: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 1jDla1-0008RX-4r for pgsql-hackers@lists.postgresql.org; Mon, 16 Mar 2020 08:58:37 +0000 Received: from mail-lf1-x144.google.com ([2a00:1450:4864:20::144]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jDlZu-0006fe-C3 for pgsql-hackers@postgresql.org; Mon, 16 Mar 2020 08:58:35 +0000 Received: by mail-lf1-x144.google.com with SMTP id b13so13285247lfb.12 for ; Mon, 16 Mar 2020 01:58:30 -0700 (PDT) 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=k8lBbEeoW6dle+5R6qucgm3O31+tqfFr850Z19sxBwk=; b=vISnvgv2PfiLtv7W/WRmO2RGivgCgkH6Wa9YNpCawEtfJeGWc5OVuI1fJcSeSW2PE8 3M3BwwjDMuRotGe2Ou8Raydaw+ph0+0B5ccA/O4xpVjcrR+FQ47RNR/8Don1b+oMph00 WmXyw/zFLPCKH+qqkM607kD+B+ns35IDEJVNILahGbehusQ7DcGlYZbQ+OuC8fdMCxMl 9fO+T3APhfhsJMBbFHKjnLIiusZ/UgWXc/4AEXgmwgRltpNIjJT/k3BTZLW4Br+b44Yz XxunU4/CtkKjYMXc+1F+owcrWNU4PylNtKzfETieRKEc0iiYX2bD+xdmetGvPWazSYpn aKwg== 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=k8lBbEeoW6dle+5R6qucgm3O31+tqfFr850Z19sxBwk=; b=Aq7h2nzJUg17YygvuhNS/5FHJYRIb/r86YQJ+L7zD/xlvz3wf60t45lqSbPDgYdvQl EFydCNuP19eq/OsaNtHWNbogdv5LAcGecdItRC7aEyPsf/9np4u/iwEUH4cMyXLKyEoF gYo/QyOKwHRrxYmEXAFALAFmlyLAro0dxocdJqdKSjSYEOF3n8wf+H9KR6HLF0PCMfwu kkc2EkMNqq0CMpdYgW9sle7x0KeEPvZrWH563xdKD2H9u4cxNUT/PQLRg0jOOmLuMYUY D7kDiF3VpJykowfLeKODNIdnuf+0Yd/D7q+tc+gwzEzAgvzAK8isDC41cY7hDIOsvZhm VybQ== X-Gm-Message-State: ANhLgQ01AjdVtKWqPERLN3Y7V4ARV1s1ybgx/UgjcQ2SWfSiOJxpSJDG D5N1DomdCzCiK8q81PV3bzkaN0cF0nMgPn6QBQUEdH64Auo1O8Qvx0UxAt6fGPrVkfEHL/OLvtc s9qYeZSfulgh/QdtnGUQBx3mTffFRJcPzfq3GnLwodlmg0g0uo8M5iid5a7+J0nLtUcBWBCMGZI a8wQDK0Mf9sxeUNPYdVRMA8+ann5t6kw== X-Google-Smtp-Source: ADFU+vs0W9Fm7CptanK9qk7zRkKsjiqMYzR6aUuPhW4QWtfYhdxtmZ6WhhJcPhtKQ2FErJqcTNQitA== X-Received: by 2002:a05:6512:287:: with SMTP id j7mr5235631lfp.93.1584349108499; Mon, 16 Mar 2020 01:58:28 -0700 (PDT) Received: from localhost.localdomain ([157.33.239.87]) by smtp.gmail.com with ESMTPSA id t3sm10483985ljg.57.2020.03.16.01.58.20 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 16 Mar 2020 01:58:27 -0700 (PDT) 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> <82851B35-E2FB-4A5D-AF35-A2B5023DB9F2@alibaba-inc.com> <77320A29-DC39-4EFE-A5E2-0A00F6A316E8@alibaba-inc.com> <7eb93026-0f35-325a-132f-ca183a5da3ac@enterprisedb.com> <7CCE7DE5-5E85-4155-9010-5EDEAE762194@alibaba-inc.com> <087bd809-e1d7-ee44-da1f-25996abad442@enterprisedb.com> <40391A22-CB8B-4143-B936-7707667F7905@alibaba-inc.com> <649a795a-49ef-ba06-dfca-68f423fcf13a@enterprisedb.com> <79D4ADC6-BE08-410B-AD6B-98840F226769@alibaba-inc.com> From: tushar Message-ID: <569f4fbe-2112-8c96-9323-ab059625429f@enterprisedb.com> Date: Mon, 16 Mar 2020 14:28:17 +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: <79D4ADC6-BE08-410B-AD6B-98840F226769@alibaba-inc.com> Content-Type: multipart/alternative; boundary="------------909C3455AF0FB51DA95C6DE9" 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. --------------909C3455AF0FB51DA95C6DE9 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi Wenjing, I have created a global table on X session but i am not able to drop from Y session ? X session - ( connect to psql terminal ) postgres=# create global temp table foo(n int); CREATE TABLE postgres=# select * from foo;  n --- (0 rows) Y session - ( connect to psql terminal ) postgres=# drop table foo; ERROR:  can not drop relation foo when other backend attached this global temp table Table has been created  so i think - user should be able to drop from another session as well without exit from X session. regards, On 3/16/20 1:35 PM, 曾文旌(义从) wrote: > > >> 2020年3月16日 下午2:23，Prabhat Sahu > > 写道： >> >> Hi Wenjing, >> Please check the below scenario, where the Foreign table on GTT not >> showing records. >> >> postgres=# create extension postgres_fdw; >> CREATE EXTENSION >> postgres=# do $d$ >>     begin >>         execute $$create server fdw foreign data wrapper postgres_fdw >> options (host 'localhost',dbname 'postgres',port >> '$$||current_setting('port')||$$')$$; >>     end; >> $d$; >> DO >> postgres=# create user mapping for public server fdw; >> CREATE USER MAPPING >> >> postgres=# create table lt1 (c1 integer, c2 varchar(50)); >> CREATE TABLE >> postgres=# insert into lt1 values (1,'c21'); >> INSERT 0 1 >> postgres=# create foreign table ft1 (c1 integer, c2 varchar(50)) >> server fdw options (table_name 'lt1'); >> CREATE FOREIGN TABLE >> postgres=# select * from ft1; >>  c1 | c2 >> ----+----- >>   1 | c21 >> (1 row) >> >> postgres=# create global temporary table gtt1 (c1 integer, c2 >> varchar(50)); >> CREATE TABLE >> postgres=# insert into gtt1 values (1,'gtt_c21'); >> INSERT 0 1 >> postgres=# create foreign table f_gtt1 (c1 integer, c2 varchar(50)) >> server fdw options (table_name 'gtt1'); >> CREATE FOREIGN TABLE >> >> postgres=# select * from gtt1; >>  c1 |   c2 >> ----+--------- >>   1 | gtt_c21 >> (1 row) >> >> postgres=# select * from f_gtt1; >>  c1 | c2 >> ----+---- >> (0 rows) >> >> -- > > I understand that postgre_fdw works similar to dblink. > postgre_fdw access to the table requires a new connection. > The data in the GTT table is empty in the newly established connection. > Because GTT shares structure but not data between connections. > > Try local temp table: > create temporary table ltt1 (c1 integer, c2 varchar(50)); > > insert into ltt1 values (1,'gtt_c21'); > > create foreign table f_ltt1 (c1 integer, c2 varchar(50)) server fdw > options (table_name 'ltt1'); > > select * from ltt1; >  c1 |   c2 > ----+--------- >   1 | gtt_c21 > (1 row) > > select * from l_gtt1; > ERROR:  relation "l_gtt1" does not exist > LINE 1: select * from l_gtt1; > > > Wenjing > > >> With Regards, >> Prabhat Kumar Sahu >> EnterpriseDB: http://www.enterprisedb.com >> > -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company --------------909C3455AF0FB51DA95C6DE9 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
Hi Wenjing,

I have created a global table on X session but i am not able to drop from Y session ?

X session - ( connect to psql terminal )
postgres=# create global temp table foo(n int);
CREATE TABLE
postgres=# select * from foo;
 n
---
(0 rows)


Y session - ( connect to psql terminal )
postgres=# drop table foo;
ERROR:  can not drop relation foo when other backend attached this global temp table

Table has been created  so i think - user should be able to drop from another session as well without exit from X session.

regards,

On 3/16/20 1:35 PM, 曾文旌(义从) wrote:


2020年3月16日 下午2:23，Prabhat Sahu <prabhat.sahu@enterprisedb.com> 写道：

Hi Wenjing,
Please check the below scenario, where the Foreign table on GTT not showing records.

postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# do $d$
    begin
        execute $$create server fdw foreign data wrapper postgres_fdw options (host 'localhost',dbname 'postgres',port '$$||current_setting('port')||$$')$$;
    end;
$d$;
DO
postgres=# create user mapping for public server fdw;
CREATE USER MAPPING

postgres=# create table lt1 (c1 integer, c2 varchar(50));
CREATE TABLE
postgres=# insert into lt1 values (1,'c21');
INSERT 0 1
postgres=# create foreign table ft1 (c1 integer, c2 varchar(50)) server fdw options (table_name 'lt1');
CREATE FOREIGN TABLE
postgres=# select * from ft1;
 c1 | c2  
----+-----
  1 | c21
(1 row)

postgres=# create global temporary table gtt1 (c1 integer, c2 varchar(50));
CREATE TABLE
postgres=# insert into gtt1 values (1,'gtt_c21');
INSERT 0 1
postgres=# create foreign table f_gtt1 (c1 integer, c2 varchar(50)) server fdw options (table_name 'gtt1');
CREATE FOREIGN TABLE

postgres=# select * from gtt1;
 c1 |   c2    
----+---------
  1 | gtt_c21
(1 row)

postgres=# select * from f_gtt1;
 c1 | c2
----+----
(0 rows)

--

I understand that postgre_fdw works similar to dblink.
postgre_fdw access to the table requires a new connection.
The data in the GTT table is empty in the newly established connection.
Because GTT shares structure but not data between connections.

Try local temp table:
create temporary table ltt1 (c1 integer, c2 varchar(50));

insert into ltt1 values (1,'gtt_c21');

create foreign table f_ltt1 (c1 integer, c2 varchar(50)) server fdw options (table_name 'ltt1');

select * from ltt1;
 c1 |   c2    
----+---------
  1 | gtt_c21
(1 row)

select * from l_gtt1;
ERROR:  relation "l_gtt1" does not exist
LINE 1: select * from l_gtt1;


Wenjing


With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com



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