pá 3. 4. 2020 v 9:52 odesílatel 曾文旌 <wenjing....@alibaba-inc.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.s...@enterprisedb.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.s...@enterprisedb.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 > > >
