Re: Postgresql 'eats' all mi data partition

On Thu, 25 Sep 2003, Javier Carlos wrote:

> Quoting Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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?

Did you use -f on the vacuumdb? If not, it did a normal vacuum (which
isn't likely to help) not a full vacuum.