DELETE versus TRUNCATE during pg_dump....

[Patrick Hatcher]

Pg 7.4.5

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?

About 60% of our tables are refreshed daily from our Filemaker database
and we don't care if one or more of these tables are empty while we do
our daily backups.

TIA

Patrick

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Patrick Hatcher
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Patrick Hatcher]

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?

Post by Tom Lane

Post by Patrick Hatcher
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Martijn van Oosterhout]

Post by Tom Lane

Post by Patrick Hatcher
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,

[Christopher Browne]

Post by Patrick Hatcher
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/>
Christopher Browne
[phone] (land)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org