Re: [PATCH] Fix: Partitioned parent index remains invalid after child indexes are repaired - Mailing list pgsql-hackers

Hi,

Thanks the report

> 6. The parent index remains `indisvalid = false` permanently. No SQL
> command can fix it.

Yes, that seems confusing. the indisvalid on a parent partition should reflect
the state of all index partitions; true if all index partitions are
valid, and false
if any index partition is invalid.

I think this can only become an issue in practice with the combination
of CREATING
a parent index ONLY ( because the parent indisvalid is marked as false
initially),
then one of the partition indexes becoming invalid, then the expectation would
be that if we REINEX/re-create the child index, a re-attach of this
index will set
the parent index indisvalid to true.

The state of an invalid parent index ( with all children being valid )
breaks the ON CONFLICT case:
```
DROP TABLE IF EXISTS pt;
CREATE TABLE pt (a int, b int) PARTITION BY RANGE (a);
CREATE TABLE pt_1 PARTITION OF pt FOR VALUES FROM (1) TO (100);
CREATE TABLE pt_2 PARTITION OF pt FOR VALUES FROM (100) TO (200);
CREATE UNIQUE INDEX pt_a_idx ON pt (a);
-- ON CONFLICT works fine here
INSERT INTO pt VALUES (1, 1);
INSERT INTO pt VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b + 1;
-- Manually invalidate the parent index
EXPLAIN ANALYZE UPDATE pg_index SET indisvalid = false WHERE
indexrelid = 'pt_a_idx'::regclass;
SELECT c.relname, i.indisvalid, i.indisready FROM pg_class c JOIN
pg_index i ON c.oid = i.indexrelid WHERE c.relname LIKE 'pt_%' ORDER
BY c.relname;
-- Now ON CONFLICT fails now
EXPLAIN ANALYZE INSERT INTO pt VALUES (1, 1) ON CONFLICT (a) DO UPDATE
SET b = EXCLUDED.b + 1;
-- ERROR: there is no unique or exclusion constraint matching the ON
CONFLICT specification
```
because of:
```
/* We require at least one indisvalid index */
if (results == NIL || !foundValid)
ereport(ERROR,
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
errmsg("there is no unique or exclusion constraint matching the ON
CONFLICT specification")));
``` in infer_arbiter_indexes() in plancat.c

> Bug 1: REINDEX Does Not Validate Parent

I don't think that a REINDEX should attempt to set the parent index indisvalid.
It seems the responsibility for this falls squarely on the ATTACH
PARTITION command,
as it currently does.

Would the right solution here be to try to have the ATTACH PARTITION  check if
the parent index is not valid, then validatePartitionedIndex() ?

````
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eec09ba1ded..a46af023689 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -22029,6 +22029,14 @@ ATExecAttachPartitionIdx(List **wqueue,
Relation parentIdx, RangeVar *name)
                free_attrmap(attmap);

                validatePartitionedIndex(parentIdx, parentTbl);
+       } else if (!parentIdx->rd_index->indisvalid)
+       {
+               /*
+                * The index is already attached but the parent isn't valid yet.
+                * Check if all partitions now have valid indexes, and if so,
+                * mark the parent index as valid.
+                */
+               validatePartitionedIndex(parentIdx, parentTbl);
        }
````
We can add some additional documentation about this in the
"ATTACH PARTITION index_name" documentation [1]
so users have a way out of this condition ?


[1] [https://www.postgresql.org/docs/current/sql-alterindex.html]

 --
Sami Imseih
Amazon Web Services (AWS)