Re: Postgresql 'eats' all mi data partition - Mailing list pgsql-bugs

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;
>..
>
>
>   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