From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: herwig.goemans@gmail.com
Subject: BUG #16976: server crash when deleting via a trigger on a foreign table
Date: Wed, 21 Apr 2021 12:39:30 +0000
Message-ID: <16976-abe0cb88252bc02e@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 16976
Logged by: Herwig Goemans
Email address: herwig.goemans@gmail.com
PostgreSQL version: 12.6
Operating system: ubuntu 18.04
Description:
Hello,
I have experienced a server crash when deleting from a table that ahs a
before trigger on it. The before trigger calls a function that deletes from
a foreign (informix) table.
The table in PG:
CREATE TABLE metadblucht.c_eenheid (
enh_id serial NOT NULL,
dat_wijziging timestamp NOT NULL,
gebr_wijziging varchar(255) NULL,
CONSTRAINT pk_enh PRIMARY KEY (enh_id)
);
Trigger and function:
create trigger td_c_eenheid before
delete
on
metadblucht.c_eenheid for each row execute function pd_c_eenheid();
create trigger ti_c_eenheid before
insert
on
metadblucht.c_eenheid for each row execute function pi_c_eenheid();
create trigger tu_c_eenheid before
update
on
metadblucht.c_eenheid for each row execute function pu_c_eenheid();
CREATE OR REPLACE FUNCTION metadblucht.pd_c_eenheid()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
delete from informix.t_water_eenheid
where enh_id = new.enh_id;
END;
$function$
;
foreign table:
CREATE FOREIGN TABLE informix.t_water_eenheid (
enh_id int4 NOT NULL
)
SERVER o_oltp_app_water
OPTIONS (table 'c_eenheid_metadblucht', database 'water', client_locale
'en_US.utf8', db_locale 'nl_be.utf8');
The statement causing the server crash:
start transaction;
delete from metadblucht.c_eenheid where enh_id = 40;
If I do a delete directly on the foreign table it works fine:
start transaction;
delete from informix.t_water_eenheid where enh_id = 40;
rollback;
This is the relevant section in the PG log file:
__2021-04-21 11:54:30 CEST LOG: server process (PID 3933) was terminated by
signal 11: Segmentation fault
__2021-04-21 11:54:30 CEST DETAIL: Failed process was running: delete from
metadblucht.c_eenheid where enh_id = 40
__2021-04-21 11:54:30 CEST LOG: terminating any other active server
processes
gebouwenbeheerdba_gebouwenbeheer_2021-04-21 11:54:30 CEST WARNING:
terminating connection because of crash of another server process
gebouwenbeheerdba_gebouwenbeheer_2021-04-21 11:54:30 CEST 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.
gebouwenbeheerdba_gebouwenbeheer_2021-04-21 11:54:30 CEST HINT: In a moment
you should be able to reconnect to the database and repeat your command.
oari_ari_2021-04-21 11:54:30 CEST WARNING: terminating connection because
of crash of another server process
oari_ari_2021-04-21 11:54:30 CEST 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.
oari_ari_2021-04-21 11:54:30 CEST HINT: In a moment you should be able to
reconnect to the database and repeat your command.
I hope someone can help.
Thanks.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, herwig.goemans@gmail.com
Subject: Re: BUG #16976: server crash when deleting via a trigger on a foreign table
In-Reply-To: <16976-abe0cb88252bc02e@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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