The following bug has been logged online:
Bug reference: 5985
Logged by: Daniel Grace
Email address: thisgenericname@gmail.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;
