Re: Issues with ON CONFLICT UPDATE and REINDEX CONCURRENTLY

From: Michail Nikolaev <michail(dot)nikolaev(at)gmail(dot)com>
To: Michael Paquier <michael(at)paquier(dot)xyz>, Noah Misch <noah(at)leadboat(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Issues with ON CONFLICT UPDATE and REINDEX CONCURRENTLY
Date: 2024-08-16 10:22:00
Message-ID: CANtu0ojT2ogosXAap+rs_6s2b3Tmu-zBWVL77Bk5gTXtTEXYEg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello, everyone.

I have updated the spec to reproduce the issue, now it includes cases with
both CREATE INDEX and REINDEX.

To run:
make -C src/test/modules/injection_points/ check

Issue reproduced on empty index, but it may happen on index of any with the
same probability.
It is not critical, of course, but in production system indexes are
regularly rebuilt using REINDEX CONCURRENTLY as recommended in
documentation [1].
In most of the cases it is done using pg_repack as far as I know.

So, in these production systems, there is no guarantee what INSERT ON
CONFLICT DO NOTHING/UPDATE will not fail with a "duplicate key value
violates unique constraint" error.

Best regards,
Mikhail.

[1]: https://www.postgresql.org/docs/current/routine-reindex.html

>

Attachment Content-Type Size
v2-0001-specs-to-reproduce-issues-with-CREATE-INDEX-REIND.patch text/x-patch 20.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2024-08-16 10:28:57 Re: Useless parameter 'cur_skey' in IndexScanOK
Previous Message Peter Eisentraut 2024-08-16 10:11:18 Re: [PATCH] Add get_bytes() and set_bytes() functions