Re: DELETE versus TRUNCATE during pg_dump....

On Tue, Dec 21, 2004 at 10:54:27AM -0500, Tom Lane wrote:
> Patrick Hatcher <pathat(at)comcast(dot)net> writes:
> > Curious: Why would a DELETE FROM tablename work while a pg_dump is
> > occurring but a TRUNCATE tablename will stay in a lock state until the
> > pg_dump is complete?
>
> TRUNCATE requires an exclusive lock on the table.
>
> This is pretty much a no-free-lunch situation: if you want the pg_dump
> to be able to dump all the rows that existed when it started, you can
> hardly expect to be able to physically remove those rows meanwhile.

I'm wondering though, in principle TRUNCATE could be written to simply
update relfilenode and create a new file and new indexes. Old
transactions will use the old table, new transactions will see an empty
table. I guess the main problem with this would knowing when to delete
the old table (and assocated indexes, etc...).

Basically, it could be equivalent to: DROP TABLE/CREATE TABLE/CREATE
INDEXes... I wonder if the file manager can handle multiple tables with
the same oid?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.