Re: [Proposal] Global temporary tables

> 2020年3月16日 下午5:31，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> 写道：
>
>
>
> On Mon, Mar 16, 2020 at 1:30 PM Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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/>