> 2020年3月16日 下午4:58，tushar <tushar.ah...@enterprisedb.com> 写道： > > Hi Wenjing, > > I have created a global table on X session but i am not able to drop from Y > session ? > > X session - ( connect to psql terminal ) > postgres=# create global temp table foo(n int); > CREATE TABLE > postgres=# select * from foo; > n > --- > (0 rows) > > > Y session - ( connect to psql terminal ) > postgres=# drop table foo; > ERROR: can not drop relation foo when other backend attached this global > temp table For now, If one dba wants to drop one GTT, he can use the view pg_gtt_attached_pids to see which backends are using this GTT. then kill these sessions with pg_terminate_backend, and he can drop this GTT.
> > Table has been created so i think - user should be able to drop from another > session as well without exit from X session. > > regards, > > On 3/16/20 1:35 PM, 曾文旌(义从) wrote: >> >> >>> 2020年3月16日 下午2:23，Prabhat Sahu <prabhat.s...@enterprisedb.com >>> <mailto:prabhat.s...@enterprisedb.com>> 写道： >>> >>> 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) >>> >>> -- >> >> I understand that postgre_fdw works similar to dblink. >> postgre_fdw access to the table requires a new connection. >> The data in the GTT table is empty in the newly established connection. >> Because GTT shares structure but not data between connections. >> >> Try local temp table: >> create temporary table ltt1 (c1 integer, c2 varchar(50)); >> >> insert into ltt1 values (1,'gtt_c21'); >> >> create foreign table f_ltt1 (c1 integer, c2 varchar(50)) server fdw options >> (table_name 'ltt1'); >> >> select * from ltt1; >> c1 | c2 >> ----+--------- >> 1 | gtt_c21 >> (1 row) >> >> select * from l_gtt1; >> ERROR: relation "l_gtt1" does not exist >> LINE 1: select * from l_gtt1; >> >> >> Wenjing >> >> >>> With Regards, >>> Prabhat Kumar Sahu >>> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/> >> > > -- > regards,tushar > EnterpriseDB https://www.enterprisedb.com/ <https://www.enterprisedb.com/> > The Enterprise PostgreSQL Company
