RE: [HACKERS] Reproducible vacuum complaint!

> -----Original Message-----
> From: owner-pgsql-hackers(at)postgreSQL(dot)org
> [mailto:owner-pgsql-hackers(at)postgreSQL(dot)org]On Behalf Of Tom Lane
> Sent: Monday, November 22, 1999 8:00 AM
> To: pgsql-hackers(at)postgreSQL(dot)org
> Subject: [HACKERS] Reproducible vacuum complaint!
>
>
> I have devised a simple manual way of reproducing that peculiar VACUUM
> notice that Oleg has been complaining about, but didn't have a reliable
> way of triggering on-demand. It looks like it is caused by some sort of
> bug in the transaction commit logic --- or maybe just VACUUM's piece of
> it, but anyway there is something mucho bad going on here.
>
> Setup:
>
> create table hits (msg_id int, nhits int);
> create index hits_pkey on hits(msg_id);
> insert into hits values(42,0);
> insert into hits values(43,0);
>
> Given this setup, you can do
>
> drop index hits_pkey;
> update hits set nhits = nhits+1 where msg_id = 42;
> create index hits_pkey on hits(msg_id);
> vacuum analyze hits;
>
> all day with no problem.
>
> BUT: start up another psql, and in that other psql begin a transaction
> block and touch anything at all --- doesn't have to be the table under
> test:
>
> begin;
> select * from int4_tbl;
>
> Now, *without committing* that other transaction, go back to the first
> psql and try again:
>
> drop index hits_pkey;
> update hits set nhits = nhits+1 where msg_id = 42;
> create index hits_pkey on hits(msg_id);
> vacuum analyze hits;
> NOTICE: Index hits_pkey: NUMBER OF INDEX' TUPLES (2) IS NOT THE
> SAME AS HEAP' (3).
> Try recreating the index.
>

Hmm,if "select * .." runs in SERIALIZABLE isolation level,the transaction
would see an old "msg_id=42" tuple(not new one). So vacuum doesn't
vanish the old "msg_id=42" tuple. Vacuum takes all running transactions
into account. But AFAIK,there's no other such stuff.
CREATE INDEX may be another one which should take all running
transactions into account.

Regards.

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