Re: don't mark indexes invalid unnecessarily

Hi,

On 2018/12/04 7:50, Alvaro Herrera wrote:
> While working on FKs pointing to partitioned tables, I noticed that in
> PG11 we fail to produce a working dump in the case of a partitioned
> table that doesn't have partitions.
>
> The attached patch fixes that.

The fix makes sense. I see that the fix is similar in spirit to:

commit 9139aa19423b736470f669e566f8ef6a7f19b801
Author: Stephen Frost <sfrost(at)snowman(dot)net>
Date: Tue Apr 25 16:57:43 2017 -0400

Allow ALTER TABLE ONLY on partitioned tables

There is no need to forbid ALTER TABLE ONLY on partitioned tables,
when no partitions exist yet. This can be handy for users who are
building up their partitioned table independently and will create actual
partitions later.

In addition, this is how pg_dump likes to operate in certain instances.

> In doing so, it breaks a test ... and
> analyzing that, it turns out that the test was broken, it wasn't testing
> what it was supposed to be testing.

Hadn't considered it this closely at the time, but...

> I patched it so that it continues
> to work (and now it tests the correct thing). To be precise, the test
> was doing this:
>
> create table parted_conflict (a int, b text) partition by range (a);
> create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a);
> create unique index on only parted_conflict_1 (a);
> create unique index on only parted_conflict (a);
> alter index parted_conflict_a_idx attach partition parted_conflict_1_a_idx;
> create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500);
>
> with the expectation that the partition would not have a proper index on
> which to run an INSERT INTO parted_conflict_1 ON CONFLICT (a), expecting
> that partition parted_conflict_1_1 would not have the index. But in
> reality parted_conflict_1_1 does have the index (and it only fails
> because the index in its parent is marked invalid). So the patch moves
> the CREATE TABLE parted_conflict_1_1 to before the indexes creation, so
> that the partition really does not have the index, and then it gets the
> expected error.

... isn't the test really trying to show that invalid unique index on
table mentioned in the insert command cannot be used to proceed with the
on conflict clause, and so isn't broken per se? But maybe I misunderstood
you.

Thanks,
Amit