trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Liu, Yuan (Yuan)" <yuan....@esgyn.cn>
Subject 回复:purgedata can not be put in a transaction?
Date Mon, 15 Jan 2018 00:50:53 GMT
+1

Yuan

-------- 原始邮件 --------
主题:RE: purgedata can not be put in a transaction?
发件人:Dave Birdsall
收件人:dev@trafodion.apache.org
抄送:

That's cool, Sean! Thanks for the suggestion...

-----Original Message-----
From: Sean Broeder [mailto:sean.broeder@esgyn.com]
Sent: Sunday, January 14, 2018 12:17 PM
To: dev@trafodion.apache.org
Subject: RE: purgedata can not be put in a transaction?

I think we could treat it like a drop.  There we disable the table during phase0 of the transaction
and allow any other phase0 operations to continue.  There may be both DML and other DDL in
the transaction.  But we don't actually truncate the table until phase2 when all participants
have voted yes.  If any participant votes no we can rollback be simply enabling the table
again.

Technically, I think it's very doable.  I would be interested to hear if others think it's
important.

Regards,
Sean

-----Original Message-----
From: Dave Birdsall [mailto:dave.birdsall@esgyn.com]
Sent: Sunday, January 14, 2018 12:08 PM
To: dev@trafodion.apache.org
Subject: RE: purgedata can not be put in a transaction?

Hi,

DROP TABLE and CREATE TABLE have been transactional in Trafodion's predecessor products since
the beginning. As have most DDL operations.

In the beginning, when Oracle was born, it was not well-understood how to make DDL transactional.
At that time there was no SQL standard that specified DDL transactional behavior either. That
has since changed -- such behavior is described in the SQL standard as an optional feature.

PURGEDATA is a bit different. PURGEDATA is not a DDL operation, rather it is the same as "DELETE
* FROM T". If you want a transactional version of PURGEDATA, you can get it by using DELETE
instead. But the implementation is quite inefficient: All the rows from T will be written
to the audit log to be used in case of rollback. PURGEDATA, being non-transactional, just
does truncates on the underlying HBase table instead; nothing goes into the audit log.

It is conceivable that we could make PURGEDATA transactional, but we'd have to under the covers
map it to something like "DROP TABLE T" + "CREATE TABLE T". And there are complications such
as preserving any dependent objects and privileges on top of that. DDL operations are expensive
in Trafodion so it might not turn out to be particularly efficient.

Dave

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn]
Sent: Friday, January 12, 2018 8:04 PM
To: dev@trafodion.apache.org
Subject: RE: purgedata can not be put in a transaction?

Thanks Anoop.

DROP TABLE and CREATE TABLE are both DDL. As I know, those DDL are non-transactional in RDBMS
such as Oracle.
So I am curious that DROP TABLE can be rollbacked in Trafodion.

By the way, it is possible that we change PURGEDATA transactional?

Best regards,
Yuan

-----Original Message-----
From: Anoop Sharma [mailto:anoop.sharma@esgyn.com]
Sent: Saturday, January 13, 2018 12:24 AM
To: dev@trafodion.apache.org
Subject: RE: purgedata can not be put in a transaction?

Most of the DDL operations are transactional and supported by traf transaction manager (DTM)
layer. This is a traf feature that enables DDL operations to be handled in an atomic transactional
way.

It means that one can do (for ex):
  begin work;
  drop table t;
  create table t1 (a..)
  rollback work;
and get to the same state that existed before the begin work.

Is there a reason or some confusion on 'drop table' being a transactional operation?

One can set autocommit to ON in a session by doing:
  set transaction autocommit ON;

This is automatically set to on from sqlci and trafci.
There may be a conn property to set it to ON as well.

anoop

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn]
Sent: Thursday, January 11, 2018 10:20 PM
To: dev@trafodion.apache.org
Subject: RE: purgedata can not be put in a transaction?

Hi Anoop,

Thanks for your feedback. It is strange that 'drop table'  is a transactional operation.

When using "purgedata" in a java application flow,  we saw below error,

ERROR[20124] This DDL operation cannot be performed if AUTOCOMMIT is OFF.

Can we set AUTOCOMMIT to ON in trafodion? How to set it?


Best regards,
Yuan

-----Original Message-----
From: Anoop Sharma [mailto:anoop.sharma@esgyn.com]
Sent: Friday, January 12, 2018 12:44 PM
To: dev@trafodion.apache.org
Subject: RE: purgedata can not be put in a transaction?

currently, purgedata(or truncate) is a non-transactional operation.
It is performed by truncating the underlying traf/hbase object.
That truncate operation cannot be undone or rolled back as it is not protected by traf transactional
layer (dtm).

'drop table' on the other hand, is a transactional operation.
One can 'rollback' a 'drop table' and get the table back.

anoop


-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn]
Sent: Thursday, January 11, 2018 8:15 PM
To: dev@trafodion.apache.org
Subject: purgedata can not be put in a transaction?

Hi Trafodioneers,

I found that purgedata can not be put in a transaction. If we put below rows after begin transaction,
error 20123 occurred.

>> begin;
>>purgedata test1;

*** ERROR[20123] A user-defined transaction has been started.

This DDL operation cannot be performed.

However, "drop" and "delete with no rollback form" can run normally. From my side, I think
both drop and purgedata are non-transactional, but why they behave different?
Is there any that we can workaournd this error?


Best regards,
Yuan

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message