Re: [Proposal] Global temporary tables

> 2020年3月24日 下午9:34，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> 写道：
>
> Hi Wenjing,
> Please check my findings(on gtt_v20.patch) as below:
>
> TestCase1: (cache lookup failed on GTT)
> -- Session1:
> postgres=# create global temporary table gtt1(c1 int) on commit delete rows;
> CREATE TABLE
>
> -- Session2:
> postgres=# drop table gtt1 ;
> DROP TABLE
>
> -- Session1:
> postgres=# create global temporary table gtt1(c1 int) on commit delete rows;
> ERROR: cache lookup failed for relation 16384
>
> TestCase2:
> -- Session1:
> postgres=# create global temporary table gtt (c1 integer) on commit preserve rows;
> CREATE TABLE
> postgres=# insert into gtt values(10);
> INSERT 0 1
>
> -- Session2:
> postgres=# drop table gtt;
> DROP TABLE
>
> I hope "session2" should not allow to perform the "DROP" operation on GTT having data.

Sorry, I introduced this bug in my refactoring.
It's been fixed.

Wenjing

>
> Behavior of GTT in Oracle Database in such scenario: For a completed transaction on GTT with(on_commit_delete_rows='FALSE') with data in a session, we will not be able to DROP from any session, we need to TRUNCATE the data first to DROP the table.
> SQL> drop table gtt;
> drop table gtt
> *
> ERROR at line 1:
> ORA-14452: attempt to create, alter or drop an index on temporary table already
> in use
>
>
> On Tue, Mar 17, 2020 at 9:16 AM 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)com <mailto:wenjing(dot)zwj(at)alibaba-inc(dot)com>> wrote:
>
>
>> 2020年3月11日 下午3:52，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com <mailto:prabhat(dot)sahu(at)enterprisedb(dot)com>> 写道：
>>
>> On Mon, Mar 9, 2020 at 10:02 PM 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)com <mailto:wenjing(dot)zwj(at)alibaba-inc(dot)com>> wrote:
>>
>>
>> Fixed in global_temporary_table_v18-pg13.patch.
>> Hi Wenjing,
>> Thanks for the patch. I have verified the previous issues with "gtt_v18_pg13.patch" and those are resolved.
>> Please find below case:
>>
>> postgres=# create sequence seq;
>> CREATE SEQUENCE
>>
>> postgres=# CREATE GLOBAL TEMPORARY TABLE gtt1(c1 int PRIMARY KEY) ON COMMIT DELETE ROWS;
>> CREATE TABLE
>>
>> postgres=# CREATE GLOBAL TEMPORARY TABLE gtt2(c1 int PRIMARY KEY) ON COMMIT PRESERVE ROWS;
>> CREATE TABLE
>>
>> postgres=# alter table gtt1 add c2 int default nextval('seq');
>> ERROR: cannot reindex global temporary tables
>>
>> postgres=# alter table gtt2 add c2 int default nextval('seq');
>> ERROR: cannot reindex global temporary tables
> reindex GTT is already supported
>
> Please check global_temporary_table_v20-pg13.patch
>
>
> Wenjing
>
>
>
>>
>> Note: We are getting this error if we have a key column(PK/UNIQUE) in a GTT, and trying to add a column with a default sequence into it.
>>
>> --
>> With Regards,
>> Prabhat Kumar Sahu
>> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
>
>
>
> --
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>