Обсуждение: [6.5.2] potentially major bug?

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)
ERROR:  Invalid XID in t_cmin

Thoughts?  Is this something that is fixed in 7.0 right now?  This is a
semi-production system right now, so the loss of information would be most
unfortunate :(

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.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 ?

If so,this may be due to the use of SnapshotNow in CREATE INDEX
command which Tom already specified a few months ago.
We have already SnapshotAny(Jan added ?) now.
Probably this would be solved by changing SnapshotNow -> SnapshotAny.

> ERROR:  Invalid XID in t_cmin
>

Seems this is also related to the potential vacuum bug Tom pointed out
about handling of HEAP_MOVED_IN(OFF) flag.  I'm suspicious about
the following stuff.  I think HEAP_MIN_INVALID tuples should always
be removed.
                               /*                                * If tuple is recently deleted then we must
notremove it                                * from relation.                                */
    if (tupgone && tuple.t_data->t_xmax >=
 
XmaxRecen
t)                               {                                       tupgone = false;


Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

On Fri, 28 Jan 2000, Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.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?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

> -----Original Message-----
> From: The Hermit Hacker [mailto:scrappy@hub.org]
>
> On Fri, 28 Jan 2000, Hiroshi Inoue wrote:
>
> > > -----Original Message-----
> > > From: owner-pgsql-hackers@postgreSQL.org
> > > [mailto:owner-pgsql-hackers@postgreSQL.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@tpf.co.jp

> > >
> > > 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.
>

For example,if you type

begin;
select .. from .. ;

and leave your seat,any vacuum won't be able to remove tuples
deleted after the 'select .. from ..'.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp  

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.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 ?
> 
> If so,this may be due to the use of SnapshotNow in CREATE INDEX
> command which Tom already specified a few months ago.
> We have already SnapshotAny(Jan added ?) now.
> Probably this would be solved by changing SnapshotNow -> SnapshotAny.
>

Oops,this is not so easy.
Comparetub_index() rejects duplicate index.
Is it an appropriate way to check visibility of heap
tuples in comapretub_index() ?

Comments ?

Regards.

Hirioshi Inoue
Inoue@tpf.co.jp