Tom Lane <t...@sss.pgh.pa.us> writes:
> Gregory Stark <st...@enterprisedb.com> writes:
>> Another thought now though. What if someone updates the pg_index entry --
>> since we never reset indcheckxmin then the new tuple will have a new xmin and
>> will suddenly become invisible again for no reason.
>
> Hmm ... if updates to pg_index entries were common then I could get
> worried about that, but they really aren't.
Fixing this for REINDEX is fairly straightforward I think. It already updates
the pg_index line to fix indisvalid and indisready. see:
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 8b14b96..c12bf6c 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2332,6 +2332,9 @@ reindex_index(Oid indexId)
* If the index is marked invalid or not ready (ie, it's from a failed
* CREATE INDEX CONCURRENTLY), we can now mark it valid. This allows
* REINDEX to be used to clean up in such cases.
+ *
+ * Also if the index was originally built using CREATE INDEX
CONCURRENTLY
+ * we want to clear the indcheckxmin field since it's no longer
relevant.
*/
pg_index = heap_open(IndexRelationId, RowExclusiveLock);
@@ -2342,10 +2345,11 @@ reindex_index(Oid indexId)
elog(ERROR, "cache lookup failed for index %u", indexId);
indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
- if (!indexForm->indisvalid || !indexForm->indisready)
+ if (!indexForm->indisvalid || !indexForm->indisready ||
indexForm->indcheckxmin)
{
indexForm->indisvalid = true;
indexForm->indisready = true;
+ indexForm->indcheckxmin = false;
simple_heap_update(pg_index, &indexTuple->t_self, indexTuple);
CatalogUpdateIndexes(pg_index, indexTuple);
}
>> Couldn't this happen if you set a table WITHOUT CLUSTER for example? Or if
>> --as possibly happened in the user's case-- you reindex the table and don't
>> find any HOT update chains but the old pg_index entry had indcheckxmin set
>> already?
>
> This is all useless guesswork until we find out whether he was using
> REINDEX or CREATE INDEX CONCURRENTLY.
Well he said he had a nightly REINDEX script. What's unknown is whether the
index was originally built with CREATE INDEX CONCURRENTLY. But I don't know
any other reason for a newly built index to go unused when the query is very
selective and then to suddenly start being used after a restart.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
