Обсуждение: Feature request: Truncate table

Поиск
Список
Период
Сортировка

Feature request: Truncate table

От
"Dann Corbit"
Дата:
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.


Re: Feature request: Truncate table

От
Larry Rosenman
Дата:
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,
compiledby GCC 2.95.3
 
(1 row)

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

ler=# 


> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 
-- 
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



Re: Feature request: Truncate table

От
Billy O'Connor
Дата:
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
bulkcopy and select into).  So one needs to be careful how this command is granted.  The same damage  (accidental
deletionof 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?

--
Billy O'Connor


Re: Feature request: Truncate table

От
Larry Rosenman
Дата:
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



Re: Feature request: Truncate table

От
"Dann Corbit"
Дата:
> -----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.


Re: Feature request: Truncate table

От
Bradley Kieser
Дата:
Well in Ingres there is a WORLD of difference! For a start, you don't 
lock out the system catalog. Secondly it is an unlogged event, so it 
beats "delete from table_name" hands down! Then, of course, it preserves 
all permissions, you keep the same OID, so views, et al, can remain in 
tact, as with other objects that referece it.

These are very important considerations in real-world applications esp. 
when a large number of objects may reference the table.


Which brings me to another point - I would dearly love to see a 
"refresh" option based on object name added to the system. This would 
check all references to a dropped object, by name, and repoint them to 
the new instance of that object (i.e. if you do a drop/create, it 
doesn't mess up your entire system if you forgot about a view or three!).


Maybe a special "drop" and "create" can be added. Like "drop to create" 
or maybe simply "recreate", which tells PG that the object should be 
treated as if it is dropped then recreated, but updating all the 
references to it or perhaps even reusing the OID?

The point being that alter table doesn't quite fill the hole (it comes 
close though) and truncate isn't a schema-changing facility, merely a 
data cropping one.

Who knows? PG may even be credited with a seriously useful extension to 
SQL that may find its way into the standard at some time!

Brad


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?
> 
> --
> Billy O'Connor
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 




Re: Feature request: Truncate table

От
Bruce Momjian
Дата:
Bradley Kieser wrote:
> Well in Ingres there is a WORLD of difference! For a start, you don't 
> lock out the system catalog. Secondly it is an unlogged event, so it 
> beats "delete from table_name" hands down! Then, of course, it preserves 
> all permissions, you keep the same OID, so views, et al, can remain in 
> tact, as with other objects that referece it.
> 
> These are very important considerations in real-world applications esp. 
> when a large number of objects may reference the table.
> 
> 
> Which brings me to another point - I would dearly love to see a 
> "refresh" option based on object name added to the system. This would 
> check all references to a dropped object, by name, and repoint them to 
> the new instance of that object (i.e. if you do a drop/create, it 
> doesn't mess up your entire system if you forgot about a view or three!).

We have actually be moving away from name-based linking so you can
rename tables and things still work.  I can see value in a relinking
system, but we would have to know the old oid and new name, I guess.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Feature request: Truncate table

От
"Christopher Kings-Lynne"
Дата:
> > 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



Re: Feature request: Truncate table

От
Hannu Krosing
Дата:
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



Re: Feature request: Truncate table

От
"Christopher Kings-Lynne"
Дата:
> > 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



Re: Feature request: Truncate table

От
Mike Mascari
Дата:
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