Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Daniel Grace" <thisgenericname(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5985: CLUSTER ... USING can fail with ERROR: index xxx does not belong to table yyy
Date: 2011-04-19 06:12:32
Message-ID: 24596.1303193552@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

In response to

Responses

pgsql-bugs by date

Next:From: anij ninanDate: 2011-04-19 08:40:47
Subject: help
Previous:From: Daniel GraceDate: 2011-04-18 21:31:40
Subject: BUG #5985: CLUSTER ... USING can fail with ERROR: index xxx does not belong to table yyy

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group