Thread: DELETE versus TRUNCATE during pg_dump....
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
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/> 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: >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 > > >
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/ > 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.