Postgres Pro
Downloads
Home   >   mailing lists

Generated column is not updated (Postgres 13) - Mailing list pgsql-bugs

From Vitaly Ustinov
Subject Generated column is not updated (Postgres 13)
Date
Msg-id CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com
Whole thread Raw
Responses Re: Generated column is not updated (Postgres 13)  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
Tree view
Hello,

I would like to report the following:
- a generated column is never updated if you pass the whole record to a stored procedure (an immutable function);
- however, it works fine if you pass individual columns to the function;
- both options work fine on Postgres 12.

I consider it a backward compatibility issue.
Please correct me if I am wrong, but I was not able to find anything related to this in the release notes for 13.x.

The background of this issue.
During the upgrade from 12.5 to 13.3 with pg_upgrade I had to drop a generated column and some other stuff. After the upgrade was successfully done, I needed to restore everything back. When I was adding the generated column all of a sudden I got a "Segmentation fault". I retried a few times with slightly different variants of the code, but each time I would get the same result - server crashed. This has been in production for many months now and never caused any issue.

2021-05-18 19:30:46 GMT [235415-9] LOG:  server process (PID 235429) was terminated by signal 11: Segmentation fault
2021-05-18 19:30:46 GMT [235415-10] DETAIL:  Failed process was running: ALTER TABLE ordering.requests_3pp ADD unique_hash bytea GENERATED ALWAYS AS (fn_ordering.calc_3pp_req_hash(requests_3pp.*)) STORED NOT NULL;
2021-05-18 19:30:46 GMT [235415-11] LOG:  terminating any other active server processes
2021-05-18 19:30:46 GMT [235422-1] WARNING:  terminating connection because of crash of another server process
2021-05-18 19:30:46 GMT [235422-2] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-05-18 19:30:46 GMT [235422-3] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2021-05-18 19:30:46 GMT [235415-12] LOG:  archiver process (PID 235423) exited with exit code 2
2021-05-18 19:30:46 GMT [235473-1] postgres@scas_acceptance FATAL:  the database system is in recovery mode
2021-05-18 19:30:46 GMT [235415-13] LOG:  all server processes terminated; reinitializing
2021-05-18 19:30:46 GMT [235474-1] LOG:  database system was interrupted; last known up at 2021-05-18 19:29:47 GMT
2021-05-18 19:30:46 GMT [235474-2] LOG:  database system was not properly shut down; automatic recovery in progress
2021-05-18 19:30:46 GMT [235474-3] LOG:  redo starts at C5B/E70000A0
2021-05-18 19:30:46 GMT [235474-4] LOG:  redo done at C5B/E706FF60
2021-05-18 19:30:46 GMT [235415-14] LOG:  database system is ready to accept connections


I started digging, each time simplifying something, and eventually I managed to make it working. I created a simple procedure which returns "id::text::bytea", then I added a new column and then I recreated the procedure with the initial business logic. And that's the reason I am reporting it, because it turned out that with Postgres 13 a generated column is never updated if you pass the whole record to the stored procedure. Like I said, it works fine in production on Postgres 12.5. As a workaround I will use a trigger, calling the same function until it's fixed.

I've run my tests against 13.2 and 13.3 (see below). But actually I think the best and easiest way of reproducing it would be using the official Docker images from Docker Hub. You can be absolutely sure it's executed in the exact same environment as I have, and that I don't use any specific configuration options etc. However, it's at your choice. This is how you can create the containers and start "psql" inside them:

Postgres 13.3:
$ docker pull postgres:13.3$ docker run -d --name pg13 -e POSTGRES_PASSWORD=postgres postgres:13.3
$ docker exec -it -u postgres pg13 psql

Postgres 12.7:
$ docker pull postgres:12.7
$ docker run -d --name pg12 -e POSTGRES_PASSWORD=postgres postgres:12.7
$ docker exec -it -u postgres pg12 psql

And then you can run the following SQL test case:
-----------------------------------------------------------
create table t(a text, b int);
insert into t values ('A', 1), ('B', 2), ('C', 3);

create or replace function calc_gen_plain(text, int) returns text
language sql immutable as
$$ select $1||'-'||$2; $$;

create or replace function calc_gen_rec_sql(t) returns text
language sql immutable as
$$ select $1.a||'-'||$1.b; $$;

create or replace function calc_gen_rec_plpgsql(t) returns text
language plpgsql immutable as
$$ begin
  raise notice '[plpgsql] a=%, b=%', $1.a, $1.b;
  return $1.a||'-'||$1.b;
end; $$;

alter table t add gen_val text not null generated always as (calc_gen_plain(a, b)) stored;
alter table t add gen_rec1 text not null generated always as (calc_gen_rec_sql(t)) stored;
alter table t add gen_rec2 text not null generated always as (calc_gen_rec_plpgsql(t)) stored;
select t.* from t;

update t set a = chr(ascii(a) + 3), b = b + 3;
select t.* from t;
-----------------------------------------------------------

As a result, after the UPDATE command I expect all generated columns to contain the same value (within each row), and that's what I actually get with Postgres 12:

 a | b | gen_val | gen_rec1 | gen_rec2
---+---+---------+----------+----------
 D | 4 | D-4     | D-4      | D-4
 E | 5 | E-5     | E-5      | E-5
 F | 6 | F-6     | F-6      | F-6

But with Postgres 13 the two last columns are not updated.
Moreover, the "raise notice" statement in the calc_gen_rec_plpgsql() function is not executed for the "UPDATE" command, so I think the function is simply not called at all.

 a | b | gen_val | gen_rec1 | gen_rec2
---+---+---------+----------+----------
 D | 4 | D-4     | A-1      | A-1
 E | 5 | E-5     | B-2      | B-2
 F | 6 | F-6     | C-3      | C-3

Please look into the attachments for detailed output.
Besides, below you will find some info about my environments where I've got the initial "Segmentation fault" issue.

Thank you!


Postgres 13.2 installed from standard binary packages.

$ apt list --installed | grep postgres

postgresql-13/now 13.2-1.pgdg18.04+1 amd64 [installed,upgradable to: 13.3-1.pgdg18.04+1]
postgresql-13-cron/now 1.3.0-2.pgdg18.04+1 amd64 [installed,upgradable to: 1.3.1-1.pgdg18.04+1]
postgresql-13-mysql-fdw/bionic-pgdg,now 2.5.5-2.pgdg18.04+1 amd64 [installed]
postgresql-13-repack/bionic-pgdg,now 1.4.6-1.pgdg18.04+1 amd64 [installed]
postgresql-client-13/now 13.2-1.pgdg18.04+1 amd64 [installed,upgradable to: 13.3-1.pgdg18.04+1]
postgresql-client-common/now 225.pgdg18.04+1 all [installed,upgradable to: 226.pgdg18.04+1]
postgresql-common/now 225.pgdg18.04+1 all [installed,upgradable to: 226.pgdg18.04+1]

$ uname -a
Linux elxajw3dxz1 5.4.0-73-generic #82~18.04.1-Ubuntu SMP Fri Apr 16 15:10:02 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.5 LTS
Release: 18.04
Codename: bionic

Postgres 13.3 installed from binary packages "13.3-2PGDG.rhel8"
posted on 18 May (https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8-x86_64/):

postgresql13-13.3-2PGDG.rhel8.x86_64.rpm
postgresql13-contrib-13.3-2PGDG.rhel8.x86_64.rpm
postgresql13-libs-13.3-2PGDG.rhel8.x86_64.rpm
postgresql13-server-13.3-2PGDG.rhel8.x86_64.rpm

$ uname -a
Linux seliiudb01107 4.18.0-193.19.1.el8_2.x86_64 #1 SMP Wed Aug 26 15:29:02 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux

$ lsb_release -a
LSB Version: :core-4.1-amd64:core-4.1-noarch
Distributor ID: RedHatEnterprise
Description: Red Hat Enterprise Linux release 8.2 (Ootpa)
Release: 8.2
Codename: Ootpa



Regards,
Vitaly Ustinov
Attachment

pgsql-bugs by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Jsonb gets extra quotes when returned from a function that break json format
Next
From: Tom Lane
Date:
Subject: Re: Generated column is not updated (Postgres 13)