Re: [Proposal] Global temporary tables

In my opinion
1 We are developing GTT according to the SQL standard, not Oracle.

2 The implementation differences you listed come from pg and oracle storage modules and DDL implementations.

2.1 issue 1 and issue 2
The creation of Normal table/GTT defines the catalog and initializes the data store file, in the case of the GTT, which initializes the store file for the current session.
But in oracle It just looks like only defines the catalog.
This causes other sessions can not drop the GTT in PostgreSQL.
This is the reason for issue 1 and issue 2, I think it is reasonable.

2.2 issue 3
I thinking the logic of drop GTT is
When only the current session is using the GTT, it is safe to drop the GTT.
because the GTT's definition and storage files can completely delete from db.
But, If multiple sessions are using this GTT, it is hard to drop GTT in session a, because remove the local buffer and data file of the GTT in other session is difficult.
I am not sure why oracle has this limitation.
So, issue 3 is reasonable.

2.3 TRUNCATE Normal table/GTT
TRUNCATE Normal table / GTT clean up the logical data but not unlink data store file. in the case of the GTT, which is the store file for the current session.
But in oracle, It just looks like data store file was cleaned up.
PostgreSQL storage is obviously different from oracle, In other words, session is detached from storage.
This is the reason for issue4 I think it is reasonable.

All in all, I think the current implementation is sufficient for dba to manage GTT.

> 2020年4月2日 下午4:45，Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> 写道：
>
> Hi All,
>
> I have noted down few behavioral difference in our GTT implementation in PG as compared to Oracle DB:
> As per my understanding, the behavior of DROP TABLE in case of "Normal table and GTT" in Oracle DB are as below:
> Any tables(Normal table / GTT) without having data in a session, we will be able to DROP from another session.
> For a completed transaction on a normal table having data, we will be able to DROP from another session. If the transaction is not yet complete, and we are trying to drop the table from another session, then we will get an error. (working as expected)
> For a completed transaction on GTT with(on commit delete rows) (i.e. no data in GTT) in a session, we will be able to DROP from another session.
> For a completed transaction on GTT with(on commit preserve rows) with data in a session, we will not be able to DROP from any session(not even from the session in which GTT is created), we need to truncate the table data first from all the session(session1, session2) which is having data.
> 1. Any tables(Normal table / GTT) without having data in a session, we will be able to DROP from another session.
> Session1:
> create table t1 (c1 integer);
> create global temporary table gtt1 (c1 integer) on commit delete rows;
> create global temporary table gtt2 (c1 integer) on commit preserve rows;
>
> Session2:
> drop table t1;
> drop table gtt1;
> drop table gtt2;
>
> -- Issue 1: But we are able to drop a simple table and failed to drop GTT as below.
> postgres=# drop table t1;
> DROP TABLE
> postgres=# drop table gtt1;
> ERROR: can not drop relation gtt1 when other backend attached this global temp table
> postgres=# drop table gtt2;
> ERROR: can not drop relation gtt2 when other backend attached this global temp table
>
> 3. For a completed transaction on GTT with(on commit delete rows) (i.e. no data in GTT) in a session, we will be able to DROP from another session.
> Session1:
> create global temporary table gtt1 (c1 integer) on commit delete rows;
>
> Session2:
> drop table gtt1;
>
> -- Issue 2: But we are getting error for GTT with(on_commit_delete_rows) without data.
> postgres=# drop table gtt1;
> ERROR: can not drop relation gtt1 when other backend attached this global temp table
>
> 4. For a completed transaction on GTT with(on commit preserve rows) with data in any session, we will not be able to DROP from any session(not even from the session in which GTT is created)
>
> Case1:
> create global temporary table gtt2 (c1 integer) on commit preserve rows;
> insert into gtt2 values(100);
> drop table gtt2;
>
> SQL> drop table gtt2;
> drop table gtt2
> *
> ERROR at line 1:
> ORA-14452: attempt to create, alter or drop an index on temporary table already in use
>
> -- Issue 3: But, we are able to drop the GTT(having data) which we have created in the same session.
> postgres=# drop table gtt2;
> DROP TABLE
>
> Case2: GTT with(on commit preserve rows) having data in both session1 and session2
> Session1:
> create global temporary table gtt2 (c1 integer) on commit preserve rows;
> insert into gtt2 values(100);
>
> Session2:
> insert into gtt2 values(200);
>
> -- If we try to drop the table from any session we should get an error, it is working fine.
> drop table gtt2;
> SQL> drop table gtt2;
> drop table gtt2
> *
> ERROR at line 1:
> ORA-14452: attempt to create, alter or drop an index on temporary table already in use
>
> postgres=# drop table gtt2 ;
> ERROR: can not drop relation gtt2 when other backend attached this global temp table
>
> -- To drop the table gtt2 from any session1/session2, we need to truncate the table data first from all the session(session1, session2) which is having data.
> Session1:
> truncate table gtt2;
> -- Session2:
> truncate table gtt2;
>
> Session 2:
> SQL> drop table gtt2;
>
> Table dropped.
>
> -- Issue 4: But we are not able to drop the GTT, even after TRUNCATE the table in all the sessions.
> -- truncate from all sessions where GTT have data.
> postgres=# truncate gtt2 ;
> TRUNCATE TABLE
>
> -- try to DROP GTT still, we are getting error.
> postgres=# drop table gtt2 ;
> ERROR: can not drop relation gtt2 when other backend attached this global temp table
>
> To drop the GTT from any session, we need to exit from all other sessions.
> postgres=# drop table gtt2 ;
> DROP TABLE
>
> Kindly let me know if I am missing something.
>
>
> On Wed, Apr 1, 2020 at 6:26 PM Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com <mailto:prabhat(dot)sahu(at)enterprisedb(dot)com>> wrote:
> Hi Wenjing,
> I hope we need to change the below error message.
>
> postgres=# create global temporary table gtt(c1 int) on commit preserve rows;
> CREATE TABLE
>
> postgres=# create materialized view mvw as select * from gtt;
> ERROR: materialized views must not use global temporary tables or views
>
> Anyways we are not allowed to create a "global temporary view",
> so the above ERROR message should change(i.e. " or view" need to be removed from the error message) something like:
> "ERROR: materialized views must not use global temporary tables"
>
> --
> 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/>