Thread: Truncate table

I have been reading about transactional DDLs at
http://archives.postgresql.org/pgsql-general/2007-06/msg00190.php.
This discussion seems to be recent though; is this feature available
in version 7.4?

In addition, CREATE TABLE, ALTER TABLE, DROP TABLE are all DDL
statements, how about TRUNCATE TABLE? The manual says it has the same
effect as DELETE so I think it is a DML but want to be sure.

Last of all, does a TRUNCATE TABLE statement issue a ROW EXCLUSIVE
lock as DELETE does?

Thank you,

Burak

"Burak Seydioglu" <buraks78@gmail.com> writes:
> I have been reading about transactional DDLs at
> http://archives.postgresql.org/pgsql-general/2007-06/msg00190.php.
> This discussion seems to be recent though; is this feature available
> in version 7.4?

It would've taken you less time to test it than to post the question.

regression=# select version();
                            version
----------------------------------------------------------------
 PostgreSQL 7.4.17 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo values(1);
INSERT 1555575 1
regression=# begin;
BEGIN
regression=# truncate table foo;
TRUNCATE TABLE
regression=# abort;
ROLLBACK
regression=# select * from foo;
 f1
----
  1
(1 row)

Looks transactional to me ...

Just FYI, 7.3 fails the same test with

regression=# truncate table foo;
ERROR:  TRUNCATE TABLE cannot run inside a transaction block

so it got fixed between 7.3 and 7.4.

            regards, tom lane