Weird behaviour after update from 12.2 to 12.3 version

From: Andrii Palko <palko1902(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Weird behaviour after update from 12.2 to 12.3 version
Date: 2020-08-17 15:42:23
Message-ID: CADdKkPmg-Hr+OxR7MV3Zwb5a_MSDgEqP1+fPfgSbK5Yf1mdPrg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello.

I've experienced strange behaviour after update minor version of postgres.
Tested on Ubuntu 18.04.

I have update query with CTE using for merge 2 similar rows by particular
columns, so it should get newer information from the new row and update the
older row with this information. After the older row is updated with new
data I delete a new row. So basically this is for updating information but
saving old IDs.

This is my query:

WITH nextRow AS (
SELECT *
FROM product_attribute_options
ORDER BY id DESC
)
UPDATE product_attribute_options
SET cost_type_id = nextRow.cost_type_id,
price = nextRow.price,
price_function = nextRow.price_function,
width = nextRow.width,
height = nextRow.height,
show_on_frontend = nextRow.show_on_frontend,
show_on_backend = nextRow.show_on_backend,
label = nextRow.label,
updated_at = now()
FROM nextRow
WHERE product_attribute_options.product_attribute_id =
nextRow.product_attribute_id
AND product_attribute_options.name = nextRow.name
AND product_attribute_options.deleted_at IS NULL
AND nextRow.deleted_at IS NULL;

Update to version 12.3 broke this query. In 12.2 it works as expected but
in version 12.3 it works conversely. It updates the newer row by
information from the old row.

As I understand such breaking changes shouldn't be in the minor version
update, so it looks strange for me, also I didn't find anything about it in
releases notes.

Hope it does make sense to you, let me know if you need anything more from
me.

Thank you for your time, I'm looking forward to hearing from you,
Andrii Palko

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message James Lucas 2020-08-17 15:51:35 Re: BUG #16582: Logical index corruption leading to apparent index scan infinite loop
Previous Message Tom Lane 2020-08-17 15:26:48 Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails