четверг, 15 марта 2012 г.

Remember TRUNCATE peculiarities

Just remember the following about TRUNCATE:
1) It is DDL operation and so is not transactional in Oracle (issuing TRUNCATE will make an implicit commit)
2) It is not MVCC-safe in PostgreSQL.

What does it mean?


alp=> create table t199 as select * from pg_opclass ;
SELECT 117
alp=> BEGIN ;
BEGIN
alp=> TRUNCATE t199;
TRUNCATE TABLE
alp=> ROLLBACK ;
ROLLBACK ^
alp=> SELECT count(*) from t199;
count
-------
117
(1 row)

So far, everything looks good: TRUNCATE was rolled back. Transactional DDL is a powerful PostgreSQL feature. But let's start parallel transaction. In first session I'll issue

alp=> \set PROMPT1 'T1> '

and in second:

alp=> \set PROMPT1 'T1> '


So, let's fun begin.
 
T1> BEGIN;
BEGIN
T1> SET transaction_isolation TO serializable;
SET
T1> UPDATE employees SET email=email;
UPDATE 103

T2> BEGIN ;
BEGIN
T2> TRUNCATE t199;
TRUNCATE TABLE
T2> COMMIT ;
COMMIT

T1> SELECT count(*) from t199;
count
-------
0
(1 row)

Oops... What has happened? Let's repeat...


T2> INSERT INTO t199 SELECT * from pg_opclass;
INSERT 0 117

T1> BEGIN;
BEGIN
T1> SET transaction_isolation TO serializable;
SET
T1> UPDATE employees SET email=email;
UPDATE 103

T2> BEGIN ;
BEGIN
T2> DELETE FROM t199;
DELETE 117
T2> COMMIT ;
COMMIT

T1> SELECT count(*) from t199;
count
-------
117
(1 row)


Do you see the difference? T2's DELETE didn't modified T1 snapshot... But TRUNCATE did. And what about "UPDATE employees"? It is just necessary to get real XID and snapshot for transaction.
So, the main idea: be attentive with "TRUNCATE". It is not just "fast DELETE", it has some not-so-evident consequences.

Комментариев нет:

Отправить комментарий