Quoting Stephan Szabo <[EMAIL PROTECTED]>: > On Thu, 25 Sep 2003, Javier Carlos wrote: > > > > ============================================================================ > > POSTGRESQL BUG REPORT TEMPLATE > > > ============================================================================ > > > > > > Your name : Javier Carlos Rivera > > Your email address : fjcarlos ( at ) correo ( dot ) insp ( dot ) mx > > > > > > System Configuration > > ---------------------- > > Architecture (example: Intel Pentium) : Intel Pentium 4 > > > > Operating System (example: Linux 2.0.26 ELF) : Debian GNU/Linux 3.0 > 2.4.21 > > > > RAM : 256 MB > > > > PostgreSQL version (example: PostgreSQL-6.3.2) : PostgreSQL-7.3.4 > > > > Compiler used (example: gcc 2.7.2) : 2.95.4 > > > > > > > > Please enter a FULL description of your problem: > > ------------------------------------------------- > > On Thursday Bruce Momjian was at Mexico; I saw him and asked about > > this problem. He told me to write to this e-mail. > > > > When I do a simple 'UPDATE' PostgreSQL 'eats' all my partition space > > of my data directory. For example: > > > > ***** My data directory is in /var > > ***** BEFORE I do the UPDATEs I got this from df: > > OPORTUNIDADES:~# df > > Filesystem 1k-blocks Used Available Use% Mounted on > > /dev/hda2 2885812 14372 2724848 1% / > > /dev/hda1 14421344 1195132 12493652 9% /var > > /dev/hda3 7692908 888560 6413568 13% /usr > > /dev/hda6 12491804 22704 11834536 1% /javier > > /dev/hda7 1494204 23936 1394364 2% /home > > > > > > ***** Then I do the UPDATEs: > > **** The updates are of this type : > > UPDATE tbl_personas SET "PIDFOLIO"=-2 WHERE "PIDFOLIO" IS NULL; > > UPDATE tbl_personas SET "P5_1"=-2 WHERE "P5_1" IS NULL; > > UPDATE tbl_personas SET "P4PAQ"=-2 WHERE "P4PAQ" IS NULL; > > UPDATE tbl_personas SET "P5_4"=-2 WHERE "P5_4" IS NULL; > > UPDATE tbl_personas SET "P5_5"=-2 WHERE "P5_5" IS NULL; > > UPDATE tbl_personas SET "P36_4"=-2 WHERE "P36_4" IS NULL; > > .. > > UPDATE table_name SET column_name = -2 WHERE column_name IS NULL; > > .. > > If you're not vacuuming, you're going to have a potentially large > number of dead rows. Does a vacuum between updates or a vacuum full at > the end bring the space usage down to something reasonable? >
I did a vacuumbdb after the updates, and the space usage didn't down to something reasonable. For example, I had a 250MB database, then I did about 300 query updates, and mi partition growed up until fill all mi data partition space of 15GB. After that I did an vacuumdb and only the space down 100MB. After that I DROPPED the database, and the space down ALL the 15GB; It's very weird, don't you think? Cheers, Javier ------------------------------------------------- http://www.insp.mx ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
