Re: [Proposal] Global temporary tables - Mailing list pgsql-hackers

From 曾文旌(义从)
Subject Re: [Proposal] Global temporary tables
Date
Msg-id 00D41F09-821E-4169-8365-5234865B6C5B@alibaba-inc.com
Whole thread Raw
In response to Re: [Proposal] Global temporary tables  (Prabhat Sahu <prabhat.sahu@enterprisedb.com>)
List pgsql-hackers


2020年3月16日 下午5:31,Prabhat Sahu <prabhat.sahu@enterprisedb.com> 写道:



On Mon, Mar 16, 2020 at 1:30 PM Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:

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.

Thanks for the explanation.
I am able to insert and select the value from f_gtt1.

 postgres=# insert into f_gtt1 values (1,'gtt_c21');
INSERT 0 1
postgres=# select * from f_gtt1;
 c1 |   c2    
----+---------
  1 | gtt_c21
(1 row)

I have one more doubt,
As you told above "GTT data is private to the session and postgres_fdw establish its own session where content of the table is empty."
Please check the below scenario, 
we can select data from the "root GTT" and "foreign GTT partitioned table" but we are unable to select data from "GTT partitioned table"
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
         119135
(1 row)

postgres=# select * from pg_gtt_attached_pids;
 schemaname | tablename | relid |  pid   
------------+-----------+-------+--------
 public     | gtt2_p1   | 73845 | 119135
 public     | gtt2_p1   | 73845 |  51482
(2 rows)


postgres=# select datid,datname,pid,application_name,query from pg_stat_activity where usename = ‘wenjing';
 datid | datname  |  pid   | application_name |                                                query                                                 
-------+----------+--------+------------------+------------------------------------------------------------------------------------------------------
 13589 | postgres | 119135 | psql             | select datid,datname,pid,application_name,query from pg_stat_activity where usename = 'wenjing';
 13589 | postgres |  51482 | postgres_fdw     | COMMIT TRANSACTION
(2 rows)

This can be explained
The postgre_fdw connection has not been disconnected, and it produced data in another session.
In other words, gtt2_p1 is empty in session 119135, but not in session 51482.



postgres=# create global temporary table gtt2 (c1 integer, c2 integer) partition by range(c1);
CREATE TABLE
postgres=# create global temporary table gtt2_p1 (c1 integer, c2 integer);
CREATE TABLE
postgres=# create foreign table f_gtt2_p1 (c1 integer, c2 integer) server fdw options (table_name 'gtt2_p1');
CREATE FOREIGN TABLE
postgres=# alter table gtt2 attach partition f_gtt2_p1 for values from (minvalue) to (10);
ALTER TABLE
postgres=# insert into gtt2 select i,i from generate_series(1,5,2)i;
INSERT 0 3
postgres=# select * from gtt2;
 c1 | c2
----+----
  1 |  1
  3 |  3
  5 |  5
(3 rows)

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

postgres=# select * from f_gtt2_p1;
 c1 | c2
----+----
  1 |  1
  3 |  3
  5 |  5
(3 rows)

Is this an expected behavior?

--

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


pgsql-hackers by date:

Previous
From: Dilip Kumar
Date:
Subject: Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager
Next
From: tushar
Date:
Subject: Re: backup manifests