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
>
