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
