Re: [Proposal] Global temporary tables

2020年3月24日 下午9:34，Prabhat Sahu <prabhat.sahu@enterprisedb.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.

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.zwj@alibaba-inc.com> wrote:

2020年3月11日 下午3:52，Prabhat Sahu <prabhat.sahu@enterprisedb.com> 写道：

On Mon, Mar 9, 2020 at 10:02 PM 曾文旌(义从) <wenjing.zwj@alibaba-inc.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