pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16
7+ messages / 5 participants
[nested] [flat]
* BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16
@ 2024-06-28 17:24 PG Bug reporting form <noreply@postgresql.org>
2024-06-28 17:57 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Christophe Pettus <xof@thebuild.com>
2024-06-28 19:13 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: PG Bug reporting form @ 2024-06-28 17:24 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: feliphepozzer@gmail.com
The following bug has been logged on the website:
Bug reference: 18526
Logged by: Feliphe Pozzer
Email address: feliphepozzer@gmail.com
PostgreSQL version: 16.3
Operating system: Ubuntu
Description:
After upgrading from PostgreSQL 15 to PostgreSQL 16, an UPDATE on our data
structure stopped functioning correctly. The data structure is randomly
generated, resulting in a variable number of updates.
Steps to Reproduce:
1 - Create the tables and indexes as per the provided script.
2 - Populate the tables with random data using the provided PL/pgSQL
blocks.
3 - Execute the following query to update the financial table
Expected Result: All records that meet the criteria should be updated
regardless of using index scan or seq scan.
Actual Result: When PostgreSQL 16 uses index scan, it fails to find and
update all records. By changing random_page_cost to a value that forces the
use of seq scan, all records are updated correctly.
Environment:
PostgreSQL Version: 16
Operating System: Ubuntu
Logs and Error Messages:
There are no specific error messages, but the unexpected behavior is
observed in the query execution plan.
Structure:
-- Creation of the financial table
CREATE TABLE financial (
id SERIAL PRIMARY KEY,
canceled BPCHAR(1),
settlement BPCHAR(1),
type VARCHAR(50),
description VARCHAR(50)
);
-- Creation of the account_movement table
CREATE TABLE account_movement (
id SERIAL PRIMARY KEY,
credit_invoice_id INT,
FOREIGN KEY (credit_invoice_id) REFERENCES financial(id)
);
-- Creation of the indexes
CREATE INDEX idx_financial_settlement ON financial USING btree
(settlement);
CREATE INDEX idx_account_movement_credit_invoice_id ON account_movement
USING btree (credit_invoice_id);
-- Populating random data for the financial table
DO
$$
BEGIN
FOR i IN 1..2500 LOOP
INSERT INTO financial (canceled, settlement, type)
VALUES (
(ARRAY['0', '1'])[floor(random() * 2 + 1)],
(ARRAY[NULL, 1])[floor(random() * 2 + 1)],
(ARRAY['type1', 'type2', 'type3', 'type4',
'type5'])[floor(random() * 5 + 1)]
);
END LOOP;
END
$$;
-- Populating random data for the account_movement table
DO
$$
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO account_movement (credit_invoice_id)
VALUES (
(ARRAY[NULL, (SELECT id FROM financial ORDER BY random() LIMIT
1)])[floor(random() * 2 + 1)]
);
END LOOP;
END
$$;
VACUUM ANALYZE financial, account_movement;
SET random_page_cost = 4;
UPDATE financial F SET description = '1'
WHERE canceled = '0' AND
settlement IS NULL AND
type = 'type5' AND
NOT EXISTS (SELECT 1 FROM account_movement AM WHERE AM.credit_invoice_id =
F.id);
SET random_page_cost = 1.1;
UPDATE financial F SET description = '1'
WHERE canceled = '0' AND
settlement IS NULL AND
type = 'type5' AND
NOT EXISTS (SELECT 1 FROM account_movement AM WHERE AM.credit_invoice_id =
F.id);
Additional Description:
I am unsure if this is a bug or incorrect usage of random_page_cost. This
same query worked perfectly in PostgreSQL 15, regardless of the execution
plan (using seq scan or index scan). To work around this issue, we had to
modify the query to correctly update the records.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16
2024-06-28 17:24 BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 PG Bug reporting form <noreply@postgresql.org>
@ 2024-06-28 17:57 ` Christophe Pettus <xof@thebuild.com>
1 sibling, 0 replies; 7+ messages in thread
From: Christophe Pettus @ 2024-06-28 17:57 UTC (permalink / raw)
To: feliphepozzer@gmail.com; pgsql-bugs@lists.postgresql.org
> On Jun 28, 2024, at 10:24, PG Bug reporting form <noreply@postgresql.org> wrote:
>
> Expected Result: All records that meet the criteria should be updated
> regardless of using index scan or seq scan.
> Actual Result: When PostgreSQL 16 uses index scan, it fails to find and
> update all records. By changing random_page_cost to a value that forces the
> use of seq scan, all records are updated correctly.
Have you tried rebuilding the index to rule out index corruption?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16
2024-06-28 17:24 BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 PG Bug reporting form <noreply@postgresql.org>
@ 2024-06-28 19:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-28 19:45 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Feliphe Pozzer <feliphepozzer@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-06-28 19:13 UTC (permalink / raw)
To: feliphepozzer@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
PG Bug reporting form <noreply@postgresql.org> writes:
> Expected Result: All records that meet the criteria should be updated
> regardless of using index scan or seq scan.
> Actual Result: When PostgreSQL 16 uses index scan, it fails to find and
> update all records. By changing random_page_cost to a value that forces the
> use of seq scan, all records are updated correctly.
I see that the problematic plan involves a Merge Right Anti Join step,
which makes me guess that this is the same bug recently reported in
https://www.postgresql.org/message-id/flat/18522-c7a8956126afdfd0%40postgresql.org
Are you in a position to try the patch posted in that thread?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16
2024-06-28 17:24 BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 PG Bug reporting form <noreply@postgresql.org>
2024-06-28 19:13 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-06-28 19:45 ` Feliphe Pozzer <feliphepozzer@gmail.com>
2024-06-28 20:32 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Feliphe Pozzer <feliphepozzer@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Feliphe Pozzer @ 2024-06-28 19:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
I am currently in the process of familiarizing myself with the steps
required to compile and run the PostgreSQL code. As this will take some
time, I wanted to let you know that I will begin testing the patch as soon
as I am ready. Thank you for your understanding and patience.
Em sex., 28 de jun. de 2024 às 16:13, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > Expected Result: All records that meet the criteria should be updated
> > regardless of using index scan or seq scan.
> > Actual Result: When PostgreSQL 16 uses index scan, it fails to find and
> > update all records. By changing random_page_cost to a value that forces
> the
> > use of seq scan, all records are updated correctly.
>
> I see that the problematic plan involves a Merge Right Anti Join step,
> which makes me guess that this is the same bug recently reported in
>
>
> https://www.postgresql.org/message-id/flat/18522-c7a8956126afdfd0%40postgresql.org
>
> Are you in a position to try the patch posted in that thread?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16
2024-06-28 17:24 BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 PG Bug reporting form <noreply@postgresql.org>
2024-06-28 19:13 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Tom Lane <tgl@sss.pgh.pa.us>
2024-06-28 19:45 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Feliphe Pozzer <feliphepozzer@gmail.com>
@ 2024-06-28 20:32 ` Feliphe Pozzer <feliphepozzer@gmail.com>
2024-06-28 20:38 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Feliphe Pozzer @ 2024-06-28 20:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
Hello Tom, I applied the patch you suggested, and it appears that the issue
has been resolved.
Em sex., 28 de jun. de 2024 às 16:45, Feliphe Pozzer <
feliphepozzer@gmail.com> escreveu:
> I am currently in the process of familiarizing myself with the steps
> required to compile and run the PostgreSQL code. As this will take some
> time, I wanted to let you know that I will begin testing the patch as soon
> as I am ready. Thank you for your understanding and patience.
>
> Em sex., 28 de jun. de 2024 às 16:13, Tom Lane <tgl@sss.pgh.pa.us>
> escreveu:
>
>> PG Bug reporting form <noreply@postgresql.org> writes:
>> > Expected Result: All records that meet the criteria should be updated
>> > regardless of using index scan or seq scan.
>> > Actual Result: When PostgreSQL 16 uses index scan, it fails to find and
>> > update all records. By changing random_page_cost to a value that forces
>> the
>> > use of seq scan, all records are updated correctly.
>>
>> I see that the problematic plan involves a Merge Right Anti Join step,
>> which makes me guess that this is the same bug recently reported in
>>
>>
>> https://www.postgresql.org/message-id/flat/18522-c7a8956126afdfd0%40postgresql.org
>>
>> Are you in a position to try the patch posted in that thread?
>>
>> regards, tom lane
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16
2024-06-28 17:24 BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 PG Bug reporting form <noreply@postgresql.org>
2024-06-28 19:13 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Tom Lane <tgl@sss.pgh.pa.us>
2024-06-28 19:45 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Feliphe Pozzer <feliphepozzer@gmail.com>
2024-06-28 20:32 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Feliphe Pozzer <feliphepozzer@gmail.com>
@ 2024-06-28 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-07-01 02:49 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-06-28 20:38 UTC (permalink / raw)
To: Feliphe Pozzer <feliphepozzer@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
Feliphe Pozzer <feliphepozzer@gmail.com> writes:
> Hello Tom, I applied the patch you suggested, and it appears that the issue
> has been resolved.
Thanks for following up! We should have an official fix in August's
minor releases.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16
2024-06-28 17:24 BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 PG Bug reporting form <noreply@postgresql.org>
2024-06-28 19:13 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Tom Lane <tgl@sss.pgh.pa.us>
2024-06-28 19:45 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Feliphe Pozzer <feliphepozzer@gmail.com>
2024-06-28 20:32 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Feliphe Pozzer <feliphepozzer@gmail.com>
2024-06-28 20:38 ` Re: BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-07-01 02:49 ` Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Guo @ 2024-07-01 02:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Feliphe Pozzer <feliphepozzer@gmail.com>; pgsql-bugs@lists.postgresql.org
On Sat, Jun 29, 2024 at 4:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Feliphe Pozzer <feliphepozzer@gmail.com> writes:
> > Hello Tom, I applied the patch you suggested, and it appears that the issue
> > has been resolved.
>
> Thanks for following up! We should have an official fix in August's
> minor releases.
Thanks for the report and the investigation!
Thanks
Richard
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-07-01 02:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-28 17:24 BUG #18526: 'UPDATE' inconsistency using index scan with 'NOT EXIST' after upgrading to PostgreSQL 16 PG Bug reporting form <noreply@postgresql.org>
2024-06-28 17:57 ` Christophe Pettus <xof@thebuild.com>
2024-06-28 19:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-28 19:45 ` Feliphe Pozzer <feliphepozzer@gmail.com>
2024-06-28 20:32 ` Feliphe Pozzer <feliphepozzer@gmail.com>
2024-06-28 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-07-01 02:49 ` Richard Guo <guofenglinux@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox