Re: [Proposal] Global temporary tables

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.

*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> wrote:

>
>
> 2020年3月11日 下午3:52，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> 写道：
>
> On Mon, Mar 9, 2020 at 10:02 PM 曾文旌(义从) <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
>
>
>

--

With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com