> 2020年3月16日 下午5:31,Prabhat Sahu <prabhat.s...@enterprisedb.com> 写道: > > > > On Mon, Mar 16, 2020 at 1:30 PM Konstantin Knizhnik > <k.knizh...@postgrespro.ru <mailto:k.knizh...@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/>