Обсуждение: BUG #17759: MERGE UPDATE statements do not cause generated columns to update
BUG #17759: MERGE UPDATE statements do not cause generated columns to update
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 17759 Logged by: Kyro Email address: adminnz@gmail.com PostgreSQL version: 15.1 Operating system: Debian (Docker) Description: I'm running the docker image postgis/postgis:15-master which is reporting as version "PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit". A table with "GENERATED ALWAYS" columns do not appear to get updated when the update is done via a MERGE statement. Below is some SQL that can reproduce this issue. -- create table with generated column using data-type postgis, and also another without postgis to show its not limited to postgis datatype columns create table latest_position ( id varchar(12) not null ,latitude numeric(11,8) not null ,longitude numeric(11,8) not null ,point geography(POINT) GENERATED ALWAYS AS (ST_POINT(longitude, latitude, 4326)::geography) STORED ,point_text text GENERATED ALWAYS AS (latitude::text || ' + ' || longitude::text) STORED ); --insert some data to test if generated columns update insert into latest_position VALUES ('1', -31.3848, 173.84848); select *, ST_Y(point::geometry), ST_X(point::geometry) from latest_position; -- ST_Y & ST_X result in -31.3848, 173.84848 respectively which is correct. -- and point_text column should be "-31.3848 & 173.84848" which it is. -- update some data to test if generated columns update update latest_position set latitude = -41.3848, longitude = 143.332211 where id = '1'; select *, ST_Y(point::geometry), ST_X(point::geometry) from latest_position; -- ST_Y & ST_X result in -41.3848, 143.332211 respectively which is correct. -- and point_text column should be "-41.3848 & 143.332211" which it is. -- update some data via merge to test if generated columns update MERGE INTO latest_position as trg USING (VALUES('1'::varchar(12),-51.3312684::numeric(11,8),173.2041482::numeric(11,8))) as src(id,latitude,longitude) ON trg.id = src.id WHEN MATCHED THEN UPDATE SET latitude = src.latitude, longitude = src.longitude; select *, ST_Y(point::geometry), ST_X(point::geometry) from latest_position; -- ST_Y & ST_X do not result in 51.3312684, 173.2041482 respectively which is incorrect. -- and point_text column should be "-41.3848 & 143.332211" but it isnt.
Re: BUG #17759: MERGE UPDATE statements do not cause generated columns to update
От
Dean Rasheed
Дата:
On Wed, 25 Jan 2023 at 13:55, PG Bug reporting form <noreply@postgresql.org> wrote: > > A table with "GENERATED ALWAYS" columns do not appear to get updated when > the update is done via a MERGE statement. > Confirmed here, using 15.1. However, it behaves as expected using the HEAD of the 15 branch. I think this got fixed as a side-effect of 3706cc97aa [1], though that commit doesn't explicitly mention MERGE, it does fix a bug in the way we identified GENERATED columns that need updating, making that process much more robust. If so, the fix should be available in 15.2, in a couple of weeks. (As an aside, perhaps we should add a regression test case for this.) Thanks for the report. Regards, Dean [1] https://github.com/postgres/postgres/commit/3706cc97aa