Re: [Proposal] Global temporary tables

> 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 <mailto: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 <http://www.enterprisedb.com/>