Re: [Proposal] Global temporary tables

pá 3. 4. 2020 v 9:52 odesílatel 曾文旌 <wenjing(dot)zwj(at)alibaba-inc(dot)com> napsal:

> 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.
>

Although the implementation of GTT is different, I think so TRUNCATE on
Postgres (when it is really finalized) can remove session metadata of GTT
too (and reduce usage's counter). It is not critical feature, but I think
so it should not be hard to implement. From practical reason can be nice to
have a tool how to refresh GTT without a necessity to close session.
TRUNCATE can be this tool.

Regards

Pavel

> 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:
>
> 1. Any tables(Normal table / GTT) without having data in a session, we
> will be able to DROP from another session.
> 2. 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)
> 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.
> 4. 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
> session2Session1:*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>
> 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
>>
>
>
> --
>
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com
>
>
>