ERROR: posting list tuple with 20 items cannot be split at offset 168
| От | Herman verschooten |
|---|---|
| Тема | ERROR: posting list tuple with 20 items cannot be split at offset 168 |
| Дата | |
| Msg-id | 8CDB73C1-E3AF-40A6-BA81-8AFE174C6402@verschooten.net обсуждение исходный текст |
| Ответы |
Re: ERROR: posting list tuple with 20 items cannot be split at offset 168
(Dmitry Dolgov <9erthalion6@gmail.com>)
Re: ERROR: posting list tuple with 20 items cannot be split at offset 168 (Peter Geoghegan <pg@bowt.ie>) Re: ERROR: posting list tuple with 20 items cannot be split at offset 168 (Andres Freund <andres@anarazel.de>) |
| Список | pgsql-bugs |
Hi,
I upgraded postgres 13 to 14+231.pgdg18.04+1 during the weekend, virtual ubuntu 20.04.3.
this morning a user reported an error when trying to change a flag in our web app (Phoenix, ecto).
I traced it back to an index issue on a boolean column.
This is the table:
tranman_production=# \d freights
Table "public.freights"
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('freights_id_seq'::regclass)
order_id | integer | | |
product_id | integer | | |
amount | integer | | |
reference_1 | character varying | | |
reference_2 | character varying | | |
reference_3 | character varying | | |
reference_4 | character varying | | |
status | integer | | | 0
created_at | timestamp without time zone | | not null |
updated_at | timestamp without time zone | | not null |
cmr | bigint | | |
amount_out | integer | | not null | 0
paused | boolean | | | false
cmr_received | boolean | | | false
Indexes:
"freights_pkey" PRIMARY KEY, btree (id)
"index_freights_on_cmr" btree (cmr)
"index_freights_on_cmr_received" btree (cmr_received)
"index_freights_on_order_id" btree (order_id)
"index_freights_on_product_id" btree (product_id)
"index_freights_on_reference_1" btree (reference_1)
"index_freights_on_reference_2" btree (reference_2)
"index_freights_on_reference_3" btree (reference_3)
"index_freights_on_reference_4" btree (reference_4)
"index_freights_on_status" btree (status)
Foreign-key constraints:
"fk_rails_21fdf332ee" FOREIGN KEY (product_id) REFERENCES products(id)
"fk_rails_af9f7b0831" FOREIGN KEY (order_id) REFERENCES orders(id)
Referenced by:
TABLE "freight_docs" CONSTRAINT "fk_rails_10448cfa4f" FOREIGN KEY (freight_id) REFERENCES freights(id)
TABLE "freight_events" CONSTRAINT "fk_rails_20c83ae774" FOREIGN KEY (freight_id) REFERENCES freights(id)
TABLE "rides" CONSTRAINT "fk_rails_a50483d9c3" FOREIGN KEY (freight_id) REFERENCES freights(id)
And this is what happens:
tranman_production=# update freights set cmr_received=false where id=49632;
ERROR: XX000: posting list tuple with 20 items cannot be split at offset 168
LOCATION: _bt_swap_posting, nbtdedup.c:1037
If I drop the index index_freights_on_cmr_received, then the update succeeds.
If I recreate the index, the error resurfaces.
Note that it does not happen for every row in the database.
Sincerely,
Herman verschooten
В списке pgsql-bugs по дате отправления:
Предыдущее
От: "K. R."Дата:
Сообщение: Re: BUG #17245: Index corruption involving deduplicated entries
Следующее
От: Dmitry DolgovДата:
Сообщение: Re: ERROR: posting list tuple with 20 items cannot be split at offset 168