> 2020年4月22日 下午10:38，Prabhat Sahu <prabhat.s...@enterprisedb.com> 写道： > > > > On Wed, Apr 22, 2020 at 2:49 PM 曾文旌 <wenjing....@alibaba-inc.com > <mailto:wenjing....@alibaba-inc.com>> wrote: >> >> 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. > Yes, I think we need a way to delete the GTT local storage without closing > the session. > > I provide the TRUNCATE tablename DROP to clear the data in the GTT and delete > the storage files. > This feature requires the current transaction to commit immediately after it > finishes truncate. > > Hi Wenjing, > Thanks for the patch(v30) for the new syntax support for (TRUNCATE table_name > DROP) for deleting storage files after TRUNCATE on GTT. > > Please check below scenarios: > > Case1: > -- session1: > postgres=# create global temporary table gtt2 (c1 integer) on commit preserve > rows; > CREATE TABLE > postgres=# create index idx1 on gtt2 (c1); > CREATE INDEX > postgres=# create index idx2 on gtt2 (c1) where c1%2 =0; > CREATE INDEX > postgres=# > postgres=# CLUSTER gtt2 USING idx1; > CLUSTER > postgres=# CLUSTER gtt2 USING idx2; > ERROR: cannot cluster on partial index "idx2" > > Case2: > -- Session2: > postgres=# CLUSTER gtt2 USING idx1; > CLUSTER > postgres=# CLUSTER gtt2 USING idx2; > CLUSTER > > postgres=# insert into gtt2 values(1); > INSERT 0 1 > postgres=# CLUSTER gtt2 USING idx1; > CLUSTER > postgres=# CLUSTER gtt2 USING idx2; > ERROR: cannot cluster on partial index "idx2" > > Case3: > -- Session2: > postgres=# TRUNCATE gtt2 DROP; > TRUNCATE TABLE > postgres=# CLUSTER gtt2 USING idx1; > CLUSTER > postgres=# CLUSTER gtt2 USING idx2; > CLUSTER > > In Case2, Case3 we can observe, with the absence of data in GTT, we are able > to "CLUSTER gtt2 USING idx2;" (having partial index) > But why does the same query fail for Case1 (absence of data)? This is expected Because TRUNCATE gtt2 DROP; The local storage file was deleted, so CLUSTER checked that there were no local files and ended the process.
Wenjing > > Thanks, > Prabhat Sahu > > > > > Wenjing > > >> >> 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 >>> <mailto: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: >>> 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.s...@enterprisedb.com >>> <mailto: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 <http://www.enterprisedb.com/> >>> >>> >>> -- >>> 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/>
smime.p7s
Description: S/MIME cryptographic signature
