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

2020年3月16日 下午5:04，Pavel Stehule <pavel.stehule@gmail.com> 写道：

po 16. 3. 2020 v 9:58 odesílatel tushar <tushar.ahuja@enterprisedb.com> napsal:

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

Table has been created  so i think - user should be able to drop from another session as well without exit from X session.

By the original design GTT was not modifiable until is used by any session. Now, you cannot to drop normal table when this table is used.

It is hard to say what is most correct behave and design, but for this moment, I think so protecting table against drop while it is used by other session is the best behave.

Maybe for next release we can introduce DROP TABLE x (FORCE) - like we have for DROP DATABASE. This behave is very similar.

Pavel

regards,

On 3/16/20 1:35 PM, 曾文旌(义从) wrote:

2020年3月16日 下午2:23，Prabhat Sahu <prabhat.sahu@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

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company