Horton
Menu

Thread

  1. BUG #17574: Attaching an invalid index to partition head make head index invalid forever

    PG Bug reporting form <noreply@postgresql.org> — 2022-08-04T23:42:54Z

    The following bug has been logged on the website:

Bug reference:      17574
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 14.4
Operating system:   Linux
Description:        

If you (operator error or script error) attach an invalid index to head of
partition index - it will make partition head index invalid forver.
I found no way to fix situation except create completely new head index and
build/attach new indexes on all partitions.

Minimal test case:
--prepare data
create table test (id integer) partition by range(id);
create table test_part_1000000 partition of test for values from (0) to
(1000000);
insert into test_part_1000000 select (random()*999999)::integer from
generate_series(1, 10000000);
create index test_id_key on only test(id);

--so far ok index invalid
\d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID
Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)

--make an invalid index on partitiomn
create index CONCURRENTLY test_part_1000000_id_key on
test_part_1000000(id);
^CCancel request sent
ERROR:  canceling statement due to user request

--attach an invalid index ??? ok/not ok? is it should be allowed at all?
alter index test_id_key attach partition test_part_1000000_id_key;

--test_id_key invalid (expected)
postgres=# \d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID
Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)

--try to fix
reindex index CONCURRENTLY test_id_key;

--no effect still invalid
postgres=# \d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID
Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)

--try to fix even more seriously
reindex index test_id_key;

--still invalid
postgres=# \d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID
Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)

--try other way around
reindex index test_part_1000000_id_key;
--and again invalid on head
postgres=# \d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID

PS: What happen in practice - attaching invalid index to head of huge (many
TB) partitioned table.

Regards,
Maxim

  2. Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever

    Maxim Boguk <maxim.boguk@gmail.com> — 2022-08-09T23:27:49Z

    On Wed, Aug 10, 2022 at 4:51 AM Robert Treat <rob@xzilla.net> wrote:

> On Fri, Aug 5, 2022 at 9:18 AM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference:      17574
> > Logged by:          Maxim Boguk
> > Email address:      maxim.boguk@gmail.com
> > PostgreSQL version: 14.4
> > Operating system:   Linux
> > Description:
> >
> > If you (operator error or script error) attach an invalid index to head
> of
> > partition index - it will make partition head index invalid forver.
> > I found no way to fix situation except create completely new head index
> and
> > build/attach new indexes on all partitions.
> >
> > Minimal test case:
> > --prepare data
> > create table test (id integer) partition by range(id);
> > create table test_part_1000000 partition of test for values from (0) to
> > (1000000);
> > insert into test_part_1000000 select (random()*999999)::integer from
> > generate_series(1, 10000000);
> > create index test_id_key on only test(id);
> >
> > --so far ok index invalid
> > \d+ test
> >                                     Partitioned table "public.test"
> >  Column |  Type   | Collation | Nullable | Default | Storage |
> Compression |
> > Stats target | Description
> >
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> >  id     | integer |           |          |         | plain   |
>    |
> >              |
> > Partition key: RANGE (id)
> > Indexes:
> >     "test_id_key" btree (id) INVALID
> > Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
> >
> > --make an invalid index on partitiomn
> > create index CONCURRENTLY test_part_1000000_id_key on
> > test_part_1000000(id);
> > ^CCancel request sent
> > ERROR:  canceling statement due to user request
> >
> > --attach an invalid index ??? ok/not ok? is it should be allowed at all?
> > alter index test_id_key attach partition test_part_1000000_id_key;
> >
> > --test_id_key invalid (expected)
> > postgres=# \d+ test
> >                                     Partitioned table "public.test"
> >  Column |  Type   | Collation | Nullable | Default | Storage |
> Compression |
> > Stats target | Description
> >
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> >  id     | integer |           |          |         | plain   |
>    |
> >              |
> > Partition key: RANGE (id)
> > Indexes:
> >     "test_id_key" btree (id) INVALID
> > Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
> >
> > --try to fix
> > reindex index CONCURRENTLY test_id_key;
> >
> > --no effect still invalid
> > postgres=# \d+ test
> >                                     Partitioned table "public.test"
> >  Column |  Type   | Collation | Nullable | Default | Storage |
> Compression |
> > Stats target | Description
> >
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> >  id     | integer |           |          |         | plain   |
>    |
> >              |
> > Partition key: RANGE (id)
> > Indexes:
> >     "test_id_key" btree (id) INVALID
> > Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
> >
> > --try to fix even more seriously
> > reindex index test_id_key;
> >
> > --still invalid
> > postgres=# \d+ test
> >                                     Partitioned table "public.test"
> >  Column |  Type   | Collation | Nullable | Default | Storage |
> Compression |
> > Stats target | Description
> >
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> >  id     | integer |           |          |         | plain   |
>    |
> >              |
> > Partition key: RANGE (id)
> > Indexes:
> >     "test_id_key" btree (id) INVALID
> > Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
> >
> > --try other way around
> > reindex index test_part_1000000_id_key;
> > --and again invalid on head
> > postgres=# \d+ test
> >                                     Partitioned table "public.test"
> >  Column |  Type   | Collation | Nullable | Default | Storage |
> Compression |
> > Stats target | Description
> >
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> >  id     | integer |           |          |         | plain   |
>    |
> >              |
> > Partition key: RANGE (id)
> > Indexes:
> >     "test_id_key" btree (id) INVALID
> >
> > PS: What happen in practice - attaching invalid index to head of huge
> (many
> > TB) partitioned table.
> >
>
> Interesting test case... fwiw I was curious how one would get
> themselves out of such a situation, and it doesn't look good. There is
> no way to detach the attached index, and you can't drop just that
> portion of the index.
>
> pagila=# drop index test_part_1000000_id_key;
> ERROR:  cannot drop index test_part_1000000_id_key because index
> test_id_key requires it
> HINT:  You can drop index test_id_key instead.
>
> I also wondered if you had additional partitions, would adding a valid
> index to a second partition, after reindexing the invalid index on the
> first partition, force a re-evaluation of the parent and set it to
> valid (since all parts are valid) but that also does not change the
> parent index. This was a bit surprising to me and unfortunately afaict
> this means the only way to fix this situation is to drop the parent
> index (and any/all child indexes which might exist).
>
> Still need to dig more to determine if there is a bug in the validity
> checking code for the parent index or if the answer is that we should
> disallow attaching invalid indexes altogether (this doesn't seem like
> a large hurdle for users, but if we don't need to add it then lets
> not).
>
>
> Robert Treat
> https://xzilla.net
>


I explored a lot different ways to fix issue (including ones you
suggested), nothing help, so I ended with creating whole new index and drop
old invalid index as only way out of issue.
Probably just catalog update set indisvalid='true' on head idex might be
sufficient but I wasn't ready for such experiments on the 15TB
mission-critical database.


-- 
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"

  3. Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever

    Robert Treat <rob@xzilla.net> — 2022-08-10T02:51:08Z

    On Fri, Aug 5, 2022 at 9:18 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      17574
> Logged by:          Maxim Boguk
> Email address:      maxim.boguk@gmail.com
> PostgreSQL version: 14.4
> Operating system:   Linux
> Description:
>
> If you (operator error or script error) attach an invalid index to head of
> partition index - it will make partition head index invalid forver.
> I found no way to fix situation except create completely new head index and
> build/attach new indexes on all partitions.
>
> Minimal test case:
> --prepare data
> create table test (id integer) partition by range(id);
> create table test_part_1000000 partition of test for values from (0) to
> (1000000);
> insert into test_part_1000000 select (random()*999999)::integer from
> generate_series(1, 10000000);
> create index test_id_key on only test(id);
>
> --so far ok index invalid
> \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
> Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
>
> --make an invalid index on partitiomn
> create index CONCURRENTLY test_part_1000000_id_key on
> test_part_1000000(id);
> ^CCancel request sent
> ERROR:  canceling statement due to user request
>
> --attach an invalid index ??? ok/not ok? is it should be allowed at all?
> alter index test_id_key attach partition test_part_1000000_id_key;
>
> --test_id_key invalid (expected)
> postgres=# \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
> Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
>
> --try to fix
> reindex index CONCURRENTLY test_id_key;
>
> --no effect still invalid
> postgres=# \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
> Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
>
> --try to fix even more seriously
> reindex index test_id_key;
>
> --still invalid
> postgres=# \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
> Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
>
> --try other way around
> reindex index test_part_1000000_id_key;
> --and again invalid on head
> postgres=# \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
>
> PS: What happen in practice - attaching invalid index to head of huge (many
> TB) partitioned table.
>

Interesting test case... fwiw I was curious how one would get
themselves out of such a situation, and it doesn't look good. There is
no way to detach the attached index, and you can't drop just that
portion of the index.

pagila=# drop index test_part_1000000_id_key;
ERROR:  cannot drop index test_part_1000000_id_key because index
test_id_key requires it
HINT:  You can drop index test_id_key instead.

I also wondered if you had additional partitions, would adding a valid
index to a second partition, after reindexing the invalid index on the
first partition, force a re-evaluation of the parent and set it to
valid (since all parts are valid) but that also does not change the
parent index. This was a bit surprising to me and unfortunately afaict
this means the only way to fix this situation is to drop the parent
index (and any/all child indexes which might exist).

Still need to dig more to determine if there is a bug in the validity
checking code for the parent index or if the answer is that we should
disallow attaching invalid indexes altogether (this doesn't seem like
a large hurdle for users, but if we don't need to add it then lets
not).


Robert Treat
https://xzilla.net

  4. Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever

    amit <amitlangote09@gmail.com> — 2022-08-10T07:46:57Z

    Hi,

On Fri, Aug 5, 2022 at 10:18 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      17574
> Logged by:          Maxim Boguk
> Email address:      maxim.boguk@gmail.com
> PostgreSQL version: 14.4
> Operating system:   Linux
> Description:
>
> If you (operator error or script error) attach an invalid index to head of
> partition index - it will make partition head index invalid forver.
> I found no way to fix situation except create completely new head index and
> build/attach new indexes on all partitions.
>
> Minimal test case:
> --prepare data
> create table test (id integer) partition by range(id);
> create table test_part_1000000 partition of test for values from (0) to
> (1000000);
> insert into test_part_1000000 select (random()*999999)::integer from
> generate_series(1, 10000000);
> create index test_id_key on only test(id);
>
> --so far ok index invalid
> \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
> Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
>
> --make an invalid index on partitiomn
> create index CONCURRENTLY test_part_1000000_id_key on
> test_part_1000000(id);
> ^CCancel request sent
> ERROR:  canceling statement due to user request
>
> --attach an invalid index ??? ok/not ok? is it should be allowed at all?
> alter index test_id_key attach partition test_part_1000000_id_key;
>
> --test_id_key invalid (expected)
> postgres=# \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
> Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
>
> --try to fix
> reindex index CONCURRENTLY test_id_key;
>
> --no effect still invalid
> postgres=# \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
> Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
>
> --try to fix even more seriously
> reindex index test_id_key;
>
> --still invalid
> postgres=# \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID
> Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)
>
> --try other way around
> reindex index test_part_1000000_id_key;
> --and again invalid on head
> postgres=# \d+ test
>                                     Partitioned table "public.test"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  id     | integer |           |          |         | plain   |             |
>              |
> Partition key: RANGE (id)
> Indexes:
>     "test_id_key" btree (id) INVALID

ISTM that the REINDEX code never looks at the indexes belonging to a
parent partitioned table, which are just catalog entries, and only
ever processes the partitions' copies of those indexes.  Perhaps, it
makes sense for REINDEX to at least update the indisvalid flag on a
parent's index using validatePartitionedIndex(), as
ATExecAttachPartitionIdx() does.

-- 
Thanks, Amit Langote
EDB: http://www.enterprisedb.com