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 1jDkfV-0003cw-E8 for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Mar 2020 08:00:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jDkfU-0005qe-4e for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Mar 2020 08:00:12 +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 1jDkfT-0005qX-OB for pgsql-hackers@lists.postgresql.org; Mon, 16 Mar 2020 08:00:11 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jDkfM-0000kO-Q9 for pgsql-hackers@postgresql.org; Mon, 16 Mar 2020 08:00:11 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id BCF5721C411B; Mon, 16 Mar 2020 10:59:46 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.27.200] (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id DD7D021C403C; Mon, 16 Mar 2020 10:59:40 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1584345586; bh=XT80mnqEosdfgsLjoLvV5PWnKGwM2LkuCYgOA+lp24Q=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=DtaM+IGhnYfop8rf2CcOWtRLz/czbHpiVFSDGiI3c3H9C9EIxEB+vIjHjOxgBEcTU H5mtQJFEnSpCLSZcgBaniSgPbwEcJJahXMsNQheILMI2ZMn4RZHXBLz4n1H8+zfHFd 6wcMHQsxLU80ql7n7gZK8K7xlLQPgMFBuLDOi98w= Subject: Re: [Proposal] Global temporary tables To: Prabhat Sahu , =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= Cc: tushar , Pavel Stehule , Robert Haas , Tomas Vondra , 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> From: Konstantin Knizhnik Message-ID: <3d0d4860-f0a4-53a1-8277-889521563724@postgrespro.ru> Date: Mon, 16 Mar 2020 10:59:40 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------308B9F91EAF66555647D81A2" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------308B9F91EAF66555647D81A2 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 16.03.2020 9:23, Prabhat Sahu wrote: > 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) > > -- > > With Regards, > Prabhat Kumar Sahu > EnterpriseDB: http://www.enterprisedb.com > It seems to be expected behavior: GTT data is private to the session and postgres_fdw establish its own session where content of the table is empty. But if you insert some data in f_gtt1, then you will be able to select this data from it because of connection cache in postgres_fdw. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------308B9F91EAF66555647D81A2 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 16.03.2020 9:23, Prabhat Sahu wrote:
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)

--

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


It seems to be expected behavior: GTT data is private to the session and postgres_fdw establish its own session where content of the table is empty.
But if you insert some data in f_gtt1, then you will be able to select this data from it because of connection cache in postgres_fdw.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--------------308B9F91EAF66555647D81A2--