Обсуждение: BUG #5985: CLUSTER ... USING can fail with ERROR: index xxx does not belong to table yyy
BUG #5985: CLUSTER ... USING can fail with ERROR: index xxx does not belong to table yyy
От
"Daniel Grace"
Дата:
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;
"Daniel Grace" <thisgenericname@gmail.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@gmail.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@gmail.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