RE: [HACKERS] [6.5.2] potentially major bug?

> -----Original Message-----
> From: The Hermit Hacker [mailto:scrappy(at)hub(dot)org]
>
> On Fri, 28 Jan 2000, Hiroshi Inoue wrote:
>
> > > -----Original Message-----
> > > From: owner-pgsql-hackers(at)postgreSQL(dot)org
> > > [mailto:owner-pgsql-hackers(at)postgreSQL(dot)org]On Behalf Of The Hermit
> > > Hacker
> > >
> > > Okay, I don't know if this has been fixed in 7.0, but:
> > >
> > > webcounter=> drop index webhit_referer_raw_url;
> > > DROP
> > > webcounter=> create index webhit_referer_raw_url on
> > > webhit_referer_raw using btree ( referrer_url );
> > > CREATE
> > > webcounter=> vacuum verbose webhit_referer_raw;
> > > NOTICE: --Relation webhit_referer_raw--
> > > NOTICE: Pages 7910: Changed 3, Reapped 2192, Empty 0, New 0; Tup
> > > 547520: Vac 43402, Keep/VTL 0/0, Crash 0, UnUsed 4871, MinLen 60,
> > > MaxLen 312; Re-using: Free/Avail. Space 4388524/4361716;
> > > EndEmpty/Avail. Pages 0/915. Elapsed 0/0 sec.
> > > NOTICE: Index webhit_referer_raw_url: Pages 5048; Tuples 547400:
> > > Deleted 0. Elapsed 0/2 sec.
> > > NOTICE: Index webhit_referer_raw_url: NUMBER OF INDEX' TUPLES
> > > (547400) IS NOT THE SAME AS HEAP' (547520)
> >
> > Hmmm,isn't there old transaction running somewhere ?
>
> not that we are aware of ... there is a daemon running that is doing COPY
> INs to the table ... how does something like that deal with a
> vacuum? Will the vacuum wait for the COPY IN to end and/or prevent a COPY
> IN from starting?
>

If a transaction read/write the target table it would be blocked by vacuum.
But vacuum couldn't know what tables other backends would read/write in
their running transactions. In MVCC old transaction have to see old deleted
tuples in SERIALIZABLE isolation level and so vacuum doesn't remove the
tuples which old transactions may see.

Note: vacuum doesn't lock database entirely but locks each table one by one.

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp