Re: [Proposal] Global temporary tables

út 25. 2. 2020 v 14:36 odesílatel Prabhat Sahu <
prabhat(dot)sahu(at)enterprisedb(dot)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(dot)zwj(at)alibaba-inc(dot)com>
> wrote:
>
>>
>>
>> 2020年2月24日 下午5:44，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> 写道：
>>
>> On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)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
>