Re: BUG #5985: CLUSTER ... USING can fail with ERROR: index xxx does not belong to table yyy

"Daniel Grace" <thisgenericname(at)gmail(dot)com> writes:
> I've had no luck reducing this to a reproducible test case, but here goes
> anyways:

> I have a lengthy script that consists of inputting a bunch of SQL files into
> Postgres in sequence. Essentially the first file is importing a database
> from MySQL and the subsequent files are doing schema alterations (separated
> out by table), with a sort of dependency solving mechanism built into the
> script.

> One such file (contents listed below, admittably not 100% useful without a
> full schema and data) makes a bunch of alterations to a table but fails when
> it reaches CLUSTER:

> psql:D:/SVN/wings/wings/branches/devpg/db/lib/course.sql:38: ERROR: index
> 178138 does not belong to table 176177

> However, this failure only occurs if the file is wrapped in a transaction
> block. Outside of a transaction block, it works fine.

I wonder if the issue is that the planner thinks the index isn't usable
yet because of HOT-chain issues. It looks to me like the described
symptoms could be produced if plancat.c's get_relation_info() decided to
ignore the index because of not passing the indcheckxmin test. And the
dependence on being inside a transaction block would be because closing
the transaction would be needed to let the index appear to be older than
the indcheckxmin limit. But if that theory is correct, this isn't a new
problem in 9.1, it goes back to 8.3. Have you been running this script
successfully on older versions?

I'm not sure how much we can do to fix this without abandoning the HOT
optimization, which seems unlikely to go over well. We can certainly
get it to produce a more helpful error message, and we could very likely
avoid the failure in more cases than we do now, but in the end it
remains the case that a newly-built index isn't necessarily usable right
away, and CLUSTER requires the index to be usable --- else you might
lose some rows.

regards, tom lane