============================================================================ 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; ..
For this example, I run 182 UPDATE queries. It took a long time to PostgreSQL to do that updates (more than 30 minutes).
***** After that I got this from df:
OPORTUNIDADES:~# df
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda2 2885812 14368 2724852 1% /
/dev/hda1 14421344 7422004 6266780 55% /var
/dev/hda3 7692908 888536 6413592 13% /usr
/dev/hda6 12491804 22704 11834536 1% /javier
/dev/hda7 1494204 23960 1394340 2% /home
******* The /var Partition growed up from 9% to 55%, from 1GB to 5.9GB !!!!
*** When I make a 'DROP DATABASE' to the database where that table belongs to, mi /var partition returns to its original size (in this example to 9%).
If I'd continue making UPDATES the size of the database will grow up until fill all my partition (from 1GB grows up to 15GB !!!).
This is by design, you need to VACUUM regularly to keep your db healthy as pointed out in the documentation.
Regards, Andreas
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
