Re: 'ERROR: attempted to update invisible tuple' from 'ALTER INDEX ... ATTACH PARTITION' on parent index

On Tue, Jul 11, 2023 at 1:22 PM Shruthi Gowda <gowdashru(at)gmail(dot)com> wrote:

> BEGIN;
>
> CREATE TABLE foo (
> id INT NOT NULL,
> ts TIMESTAMP WITH TIME ZONE NOT NULL
> ) PARTITION BY RANGE (ts);
>
> CREATE TABLE foo_2023 (
> id INT NOT NULL,
> ts TIMESTAMP WITH TIME ZONE NOT NULL
> );
>
> ALTER TABLE ONLY foo
> ATTACH PARTITION foo_2023
> FOR VALUES FROM ('2023-01-01 00:00:00+09') TO ('2024-01-01 00:00:00+09');
>
> CREATE UNIQUE INDEX pk_foo
> ON ONLY foo USING btree (id, ts);
>
> ALTER TABLE ONLY foo REPLICA IDENTITY USING INDEX pk_foo;
>
> CREATE UNIQUE INDEX foo_2023_id_ts_ix ON foo_2023 USING btree (id, ts);
>
> ALTER INDEX pk_foo ATTACH PARTITION foo_2023_id_ts_ix;
>
>
This example confused me quite a bit when I first read it. I think that the
documentation for CREATE INDEX .. ONLY is pretty inadequate. All it says is
"Indicates not to recurse creating indexes on partitions, if the table is
partitioned. The default is to recurse." But that would just create a
permanently empty index, which is of no use to anyone. I think we should
somehow explain the intent of this, namely that this creates an initially
invalid index which can be made valid by using ALTER INDEX ... ATTACH
PARTITION once per partition.

--
Robert Haas
EDB: http://www.enterprisedb.com