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

The following bug has been logged online:

Bug reference: 5985
Logged by: Daniel Grace
Email address: thisgenericname(at)gmail(dot)com
PostgreSQL version: 9.1a5
Operating system: Win7 x64, also seen on Debian
Description: CLUSTER ... USING can fail with ERROR: index xxx does
not belong to table yyy
Details:

[Apologies if this duplicates. NoScript apparently thought me submitting
this was XSS, so I'm not sure if the first report went through or not.]

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

My theory is this has something to do with the new CLUSTER change, in
conjunction with the fact that an index with the specified name is being
dropped and then created in the same transaction. The same dataset on 9.0
works without any issues.

Partial SQL is below. Note that it works when not wrapped with a
transaction block.

-- @Requires: data, flags
-- @Provides: course

CREATE TRIGGER _restrict BEFORE UPDATE OF id ON course EXECUTE PROCEDURE
update_restricted__tproc();
ALTER TABLE course
ALTER credit_designation_inherit TYPE BOOLEAN USING
credit_designation_inherit<>0,
ALTER credit_designation_inherit SET DEFAULT TRUE,
ADD FOREIGN KEY(gid) REFERENCES group_info(id) ON UPDATE CASCADE ON DELETE
RESTRICT,
ALTER section DROP NOT NULL,
ALTER slp_mindays DROP NOT NULL,
ALTER credits DROP NOT NULL,
DROP COLUMN IF EXISTS lock_token
;

UPDATE course SET section=NULLIF(section, 0), credits=NULLIF(credits,0),
slp_mindays=NULLIF(slp_mindays,0);

--These aren't immutable.
--CREATE INDEX course_ix_start ON course ((startdate+starttime));
--CREATE INDEX course_ix_end ON course ((enddate+endtime));

DROP INDEX course_ix_flags;
DROP INDEX course_ix_location;

DROP INDEX course_ix_credit_designation_inherit;
DROP INDEX course_ix_grademin;
CREATE INDEX course_ix_grademin ON course(grademin);
CREATE INDEX course_ix_grademax ON course(grademax);

DROP INDEX course_ix_year;
CREATE INDEX course_ix_year ON course(year);

DROP INDEX course_ix_origin_course;
CREATE INDEX course_ix_origin_course ON course(origin_course) WHERE
origin_course IS NOT NULL;

DROP INDEX course_ix_origin_op;
CREATE INDEX course_ix_origin_op ON course(origin_op) WHERE origin_op IS NOT
NULL;

CLUSTER VERBOSE course USING course_ix_year;
ANALYZE course;

"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

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

On 9.0.

Though... now that I think about it, I found a bug with the script itself (I
was braindead when I wrote the commandline parser and -pvx did not mean -p
-v -x) that was causing it to not always wrap files in transaction blocks
when it was told to, so it is *possible* 9.0 would be likewise affected...
since the problem only is visible when the index is touched in the same
transaction. I don't have the means to test against it right now, however.

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

Dewin <thisgenericname(at)gmail(dot)com> writes:
>> 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?

> On 9.0.

Thanks. On reflection I concluded that this is a new bug in 9.1 --- or
at least, this manifestation is new in 9.1.
http://archives.postgresql.org/pgsql-hackers/2011-04/msg00970.php

regards, tom lane

I wrote:
> "Daniel Grace" <thisgenericname(at)gmail(dot)com> writes:
>> 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.

On closer inspection this seems to be just insufficient consideration of
corner cases. We can handle it with a pretty trivial fix in
plan_cluster_use_sort. Thanks for the bug report!

regards, tom lane