Re: problems with table corruption continued

"Brian Hirt" <bhirt(at)mobygames(dot)com> writes:
> [ example case that creates a duplicate tuple ]

Got it. The problem arises in this section of vacuum.c, near the bottom
of repair_frag:

if (!(tuple.t_data->t_infomask & HEAP_XMIN_COMMITTED))
{
if ((TransactionId) tuple.t_data->t_cmin != myXID)
elog(ERROR, "Invalid XID in t_cmin (3)");
if (tuple.t_data->t_infomask & HEAP_MOVED_OFF)
{
itemid->lp_flags &= ~LP_USED;
num_tuples++;
}
else
elog(ERROR, "HEAP_MOVED_OFF was expected (2)");
}

This is trying to get rid of the original copy of a tuple that's been
moved to another page. The problem is that your index function causes a
table scan, which means that by the time control gets here, someone else
has looked at this tuple and marked it good --- so the initial test of
HEAP_XMIN_COMMITTED fails, and the tuple is never removed!

I would say that it's incorrect for vacuum.c to assume that
HEAP_XMIN_COMMITTED can't become set on HEAP_MOVED_OFF/HEAP_MOVED_IN
tuples during the course of vacuum's processing; after all, the xmin
definitely does refer to a committed xact, and we can't realistically
assume that we know what processing will be induced by user-defined
index functions. Vadim, what do you think? How should we fix this?

In the meantime, Brian, I think you ought to get rid of your index
CREATE INDEX "developer_aka_search_idx" on "developer_aka" using btree ( developer_aka_search_name ("developer_aka_id") "varchar_ops" );
I do not think this index is well-defined: an index function ought
to have the property that its output depends solely on its input and
cannot change over time. This function cannot make that claim.
(In 7.2, you can't even create the index unless you mark the function
iscachable, which is really a lie.) I'm not even real sure what you
expect the index to do for you...

I do not know whether this effect explains all the reports of duplicate
tuples we've seen in the last few weeks. We need to ask whether the
other complainants were using index functions that tried to do table
scans.

regards, tom lane