TRUNCATE, gestion transactionnelle

Tronquer joyeusement

      C’est seulement à partir de SQL:2008 que la norme décrit TRUNCATE TABLE, une commande DDL (Data Definition Language) permettant de supprimer l’ensemble des lignes d’une table. TRUNCATE existe cependant depuis bien plus longtemps sous Oracle comme sous PostgreSQL. Quelles sont les différences dans son implémentation entre ces SGBD ?
      Tout d’abord un premier test avec PostgreSQL 9.6 :

-- Note : \set AUTOCOMMIT off dans .psqlrc SELECT version(); version ------------------------------------------------------------------------------------------ PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 ligne) CREATE TABLE t1(c1 SERIAL PRIMARY KEY, c2 INTEGER); CREATE TABLE INSERT INTO t1(c2) VALUES(generate_series(1,999)); INSERT 0 999 COMMIT; COMMIT TRUNCATE TABLE t1; TRUNCATE TABLE ROLLBACK; ROLLBACK SELECT count(*) FROM t1; count ------- 999 (1 ligne) TRUNCATE TABLE t1; TRUNCATE TABLE COMMIT; COMMIT SELECT count(*) FROM t1; count ------- 0 (1 ligne)

      Avec PostgreSQL, TRUNCATE est une instruction comme une autre dans le sens où elle peut être utilisée au sein de transactions. Il est donc nécessaire de valider un TRUNCATE avec COMMIT mais il est aussi possible d’annuler un TRUNCATE avec un ROLLBACK.
      A présent le même test avec Oracle Database dans sa version 12.1 :

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options CREATE TABLE t1(c1 INTEGER GENERATED AS IDENTITY PRIMARY KEY, c2 INTEGER); Table created. INSERT INTO t1(c2) SELECT ROWNUM FROM all_objects WHERE ROWNUM < 1000; 999 rows created. COMMIT; Commit complete. SELECT to_char(last_ddl_time, 'DD/MM/YYYY HH24:MI:SS') FROM user_objects WHERE object_name = 'T1'; TO_CHAR(LAST_DDL_TI ------------------- 18/10/2016 11:17:40 TRUNCATE TABLE t1; Table truncated. SELECT to_char(last_ddl_time, 'DD/MM/YYYY HH24:MI:SS') FROM user_objects WHERE object_name = 'T1'; TO_CHAR(LAST_DDL_TI ------------------- 18/10/2016 11:22:10 ROLLBACK; Rollback complete. SELECT count(*) FROM t1; COUNT(*) ---------- 0

      TRUNCATE est une commande DDL (Data Definition Language) et non une commande DML (Data Manipulation Language). Hors, une validation implicite est réalisée par Oracle Database avant et après chaque commande DDL. Il est donc impossible d’annuler un TRUNCATE en effectuant un ROLLBACK sous Oracle. C’est une première différence très importante avec PostgreSQL. Dans une deuxième partie, nous verrons les différences dans la gestion de la sécurité entre les 2 SGBD au niveau de TRUNCATE.

Mise à jour : 18/10/2016