Re: Feature request: Truncate table

On Wed, 2002-06-12 at 13:37, Billy O'Connor wrote:

Deletion of data from a PostgreSQL table is very slow.

It would be nice to have a very fast delete like "truncate table."

Now, truncate is a very dangerous command because it is not logged (but
the same is true for other operations like bulk copy and select into).
So one needs to be careful how this command is granted. The same damage
(accidental deletion of all data) can be done by drop table just as
easily.

I frequently have to do this right now in PostgreSQL, but I simply
emulate it by drop table/create table.

What is a TRUNCATE TABLE but a drop create anyway? Is there some
technical difference?

It doesn't kill indexes/triggers/constraints/Foreign Key Stuff, etc.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

-----Original Message-----
From: Larry Rosenman [mailto:ler@lerctr.org]
Sent: Wednesday, June 12, 2002 12:36 PM
To: Dann Corbit
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Feature request: Truncate table

On Wed, 2002-06-12 at 14:32, Dann Corbit wrote:

Deletion of data from a PostgreSQL table is very slow.

It would be nice to have a very fast delete like "truncate table."

Now, truncate is a very dangerous command because it is not

logged (but

the same is true for other operations like bulk copy and

select into).

So one needs to be careful how this command is granted.

The same damage

(accidental deletion of all data) can be done by drop table just as
easily.

I frequently have to do this right now in PostgreSQL, but I simply
emulate it by drop table/create table.

It's there:
$ psql
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

ler=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.2.1 on i386-portbld-freebsd4.6, compiled by GCC 2.95.3
(1 row)

ler=# \h truncate
Command: TRUNCATE
Description: empty a table
Syntax:
TRUNCATE [ TABLE ] name

ler=#

Well bust my buttons! Now that's service!
;-)

I am busily doing a Win32 port of PostgreSQL 7.2.1 right now, so that is
wonderful news.

What is a TRUNCATE TABLE but a drop create anyway? Is there some
technical difference?

It doesn't kill indexes/triggers/constraints/Foreign Key Stuff, etc.

Hrm - last time I checked it did...

Chris

Hrm - last time I checked it did...

Two questions :

When was the last time ?

7.1

It did what ?

Drops triggers and stuff.

OK, I did a check and it looks like it's fixed in 7.2 at least. Sorry for
the false alarm...

Chris

Christopher Kings-Lynne wrote:

Hrm - last time I checked it did...

Two questions :

When was the last time ?

7.1

It did what ?

Drops triggers and stuff.

OK, I did a check and it looks like it's fixed in 7.2 at least. Sorry for
the false alarm...

It has never "dropped triggers and stuff", so there was nothing to fix.
All TRUNCATE TABLE has ever done, since the patch was submitted, was to
truncate the underlying relation file and the associated index files,
and reinitialize the indexes. It has been changed to be disallowed in
transactions involving tables not created in the same transaction, but
that's about it. People have argued that if there are *RI* triggers on a
table, that TRUNCATE should be disallowed, as in Oracle. But TRUNCATE
from inception to date has never dropped triggers...

Mike Mascari
mascarm@mascari.com

On Thu, 2002-06-13 at 03:47, Christopher Kings-Lynne wrote:

What is a TRUNCATE TABLE but a drop create anyway? Is there some
technical difference?

It doesn't kill indexes/triggers/constraints/Foreign Key Stuff, etc.

Hrm - last time I checked it did...

Two questions :

When was the last time ?

It did what ?

-------------
Hannu