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

============================================================================
                        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 !!!).

    I don't know if this problem is related to the migration from SPSS to
Dbase to SQL Server to PostgreSQL that I had to do. I have also used
StatTransfer to migrate directly from SPSS to PostgreSQL, but I obtained the
same bad results.


        Thanks,

        Javier



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
-----------------------------------------------------------------------
    I have done five times all the process from drop the database, migrate
the database and then do the updates, and the result is always the same.



If you know how this problem might be fixed, list the solution below:
----------------------------------------------------------------------


-------------------------------------------------
http://www.insp.mx