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

Quoting Stephan Szabo <sszabo@megazone.bigpanda.com>:

> 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