Re: lots of updates on small table

Hi all,

> No wonder, considering that your "less than 10 rows" table contains
> something upwards of 100000 tuples:
>
> > INFO:  --Relation public.plc_fldio--
> > INFO:  Pages 1221: Changed 3, reaped 256, Empty 0, New 0; Tup 108137: Vac 4176, Keep/VTL 108133/108133, UnUsed 19,
MinLen84, MaxLen 84; Re-using: Free/Avail. Space 445176/371836; EndEmpty/Avail. Pages 0/256. 
> >         CPU 0.04s/0.14u sec elapsed 0.18 sec.
>
> What you need to do is find out why VACUUM is unable to reclaim all
> those dead row versions.  The reason is likely that some process is
> sitting on a open transaction for days at a time.
>
Cheers mate, that was one of our theories but we weren't sure if it'd be
worth rebuilding everything to check.  We've been compiling without the
-t (autocommit) flag to ecpg, and i believe what's happening is
sometimes a transaction is begun and then the processes cycle around
doing hardware i/o and never commit or only commit way too late.  What
we're going to try now is remove all the begins and commits from the
code and compile with -t to make sure that any updates happen
immediately.  Hopefully that'll avoid any hanging transactions.

We'll also set up a 10-minutely vacuum (not full) as per some other
suggestions here.  I'll let you know how it goes - we'll probably slot
everything in on Monday so we have a week to follow it.

Thanks everyone
Alison