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