Re: [Proposal] Global temporary tables

Hi,

I have created two  global temporary tables like this -

Case 1-
postgres=# create global  temp table foo(n int) *with 
(on_c*ommit_delete_rows='true');
CREATE TABLE

Case 2-
postgres=# create global  temp table bar1(n int) *on c*ommit delete rows;
CREATE TABLE


but   if i try to do the same having only 'temp' keyword , Case 2 is 
working fine but getting this error  for case 1 -

postgres=# create   temp table foo1(n int) with 
(on_commit_delete_rows='true');
ERROR:  regular table cannot specifie on_commit_delete_rows
postgres=#

postgres=#  create   temp table bar1(n int) on commit delete rows;
CREATE TABLE

i think this error message need to be more clear .

regards,
tushar

On 2/25/20 7:19 PM, Pavel Stehule wrote/:
>
>
> út 25. 2. 2020 v 14:36 odesílatel Prabhat Sahu 
> <prabhat.sahu@enterprisedb.com <mailto:prabhat.sahu@enterprisedb.com>> 
> napsal:
>
>     Hi All,
>
>     Please check the below findings on GTT.
>     _-- Scenario 1:_
>     Under "information_schema", We are not allowed to create
>     "temporary table", whereas we can CREATE/DROP "Global Temporary
>     Table", is it expected ?
>
>
> It is ok for me. temporary tables should be created only in 
> proprietary schema. For GTT there is not risk of collision, so it can 
> be created in any schema where are necessary access rights.
>
> Pavel
>
>
>     postgres=# create temporary table information_schema.temp1(c1 int);
>     ERROR:  cannot create temporary relation in non-temporary schema
>     LINE 1: create temporary table information_schema.temp1(c1 int);
>                                    ^
>
>     postgres=# create global temporary table
>     information_schema.temp1(c1 int);
>     CREATE TABLE
>
>     postgres=# drop table information_schema.temp1 ;
>     DROP TABLE
>
>     _-- Scenario 2:_
>     Here I am getting the same error message in both the below cases.
>     We may add a "global" keyword with GTT related error message.
>
>     postgres=# create global temporary table gtt1 (c1 int unique);
>     CREATE TABLE
>     postgres=# create temporary table tmp1 (c1 int unique);
>     CREATE TABLE
>
>     postgres=# create temporary table tmp2 (c1 int references gtt1(c1) );
>     ERROR:  constraints on temporary tables may reference only
>     temporary tables
>
>     postgres=# create global temporary table gtt2 (c1 int references
>     tmp1(c1) );
>     ERROR:  constraints on temporary tables may reference only
>     temporary tables
>
>     Thanks,
>     Prabhat Sahu
>
>     On Tue, Feb 25, 2020 at 2:25 PM 曾文旌(义从)
>     <wenjing.zwj@alibaba-inc.com <mailto:wenjing.zwj@alibaba-inc.com>>
>     wrote:
>
>
>
>>         2020年2月24日 下午5:44，Prabhat Sahu
>>         <prabhat.sahu@enterprisedb.com
>>         <mailto:prabhat.sahu@enterprisedb.com>> 写道：
>>
>>         On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从)
>>         <wenjing.zwj@alibaba-inc.com
>>         <mailto:wenjing.zwj@alibaba-inc.com>> wrote:
>>
>>             Hi,
>>             I have started testing the "Global temporary table" feature,
>>             That's great, I see hope.
>>             from "gtt_v11-pg13.patch". Below is my findings:
>>
>>             -- session 1:
>>             postgres=# create global temporary table gtt1(a int);
>>             CREATE TABLE
>>
>>             -- seeeion 2:
>>             postgres=# truncate gtt1 ;
>>             ERROR:  could not open file "base/13585/t3_16384": No
>>             such file or directory
>>
>>             is it expected?
>>
>>             Oh ,this is a bug, I fixed it.
>>
>>         Thanks for the patch.
>>         I have verified the same, Now the issue is resolved with v12
>>         patch.
>>
>>         Kindly confirm the below scenario:
>>
>>         postgres=# create global temporary table gtt1 (c1 int unique);
>>         CREATE TABLE
>>
>>         postgres=# create global temporary table gtt2 (c1 int
>>         references gtt1(c1) );
>>         ERROR:  referenced relation "gtt1" is not a global temp table
>>
>>         postgres=# create table tab2 (c1 int references gtt1(c1) );
>>         ERROR:  referenced relation "gtt1" is not a global temp table
>>
>>         Thanks,
>>         Prabhat Sahu
>
>         GTT supports foreign key constraints
>         in global_temporary_table_v13-pg13.patch
>
>
>         Wenjing
>
>
>
>
>
>     -- 
>
>     With Regards,
>     Prabhat Kumar Sahu
>     EnterpriseDB: http://www.enterprisedb.com
>     <http://www.enterprisedb.com/>
>

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