DELETE versus TRUNCATE during pg_dump....

Patrick Hatcher <pathat@comcast.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.

regards, tom lane

pathat@comcast.net (Patrick Hatcher) 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?

DELETE FROM merely has to mark all the tuples as dead, which requires
no control over the table as a whole.

TRUNCATE essentially reinitializes the table as empty, which does need
a (if brief) lock on the table.

Yeah, you can't TRUNCATE while the dump is running...
--
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/&gt;
Christopher Browne
(416) 673-4124 (land)

Hey there Tom thanks for the answer.

However, as you saw I wrote this early in the morning and forgot an
important piece of information:
The table at the time of the truncate was not being dumped. I could see
in pg_stat_activity that it was chugging away at one of the 63M row
tables I have.

Does this make a difference?

Tom Lane wrote:

On Tue, Dec 21, 2004 at 10:54:27AM -0500, Tom Lane wrote:

Patrick Hatcher <pathat@comcast.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@svana.org> http://svana.org/kleptog/