Re: [Proposal] Global temporary tables

po 16. 3. 2020 v 9:58 odesílatel tushar <tushar(dot)ahuja(at)enterprisedb(dot)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(dot)sahu(at)enterprisedb(dot)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
>
>