BUG #14808: V10-beta4, backend abort

The following bug has been logged on the website:

Bug reference: 14808
Logged by: Philippe BEAUDOIN
Email address: phb07(at)apra(dot)asso(dot)fr
PostgreSQL version: 10beta4
Operating system: Linux
Description:

Hi all,

While continuing to play with transition tables in statement level trigger,
I have encountered what looks like a backend abort.

I have been able to reproduce the case with the following simple script:

#!/bin/sh
export PGHOST=localhost
export PGPORT=5410

dropdb test
createdb test

psql test <<*EOF*
\set ON_ERROR_STOP on

CREATE OR REPLACE FUNCTION create_tbl(grpdef_schema TEXT, grpdef_tblseq
TEXT)
RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS
\$_create_tbl\$
DECLARE
v_fullTableName TEXT;
v_logTableName TEXT;
v_logFnctName TEXT;
v_colList1 TEXT;
v_colList2 TEXT;
v_colList3 TEXT;
v_colList4 TEXT;
BEGIN
-- build the different name for table, trigger, functions,...
v_fullTableName = grpdef_schema || '.' || grpdef_tblseq;
v_logTableName = grpdef_tblseq || '_log';
v_logFnctName = grpdef_tblseq || '_log_idx';
-- build the tables's columns lists
SELECT string_agg('tbl.' || col_name, ','),
string_agg('o.' || col_name || ' AS ' || col_name_o || ', n.' ||
col_name || ' AS ' || col_name_n, ','),
string_agg('r.' || col_name_o, ','),
string_agg('r.' || col_name_n, ',')
INTO v_colList1, v_colList2, v_colList3, v_colList4 FROM (
SELECT quote_ident(attname) AS col_name, quote_ident('o_' || attname)
AS col_name_o, quote_ident('n_' || attname) AS col_name_n
FROM pg_catalog.pg_attribute
WHERE attrelid = v_fullTableName::regclass
AND attnum > 0 AND NOT attisdropped
ORDER BY attnum) AS t;
-- create the log table: it looks like the application table, with some
additional technical columns
EXECUTE 'DROP TABLE IF EXISTS ' || v_logTableName;
EXECUTE 'CREATE TABLE ' || v_logTableName
|| ' (LIKE ' || v_fullTableName || ') ';
EXECUTE 'ALTER TABLE ' || v_logTableName
|| ' ADD COLUMN verb VARCHAR(3),'
|| ' ADD COLUMN tuple VARCHAR(3)';
-- create the log function
EXECUTE 'CREATE OR REPLACE FUNCTION ' || v_logFnctName || '() RETURNS
TRIGGER AS \$logfnct\$'
|| 'DECLARE'
|| ' r RECORD;'
|| 'BEGIN'
|| ' IF (TG_OP = ''DELETE'') THEN'
|| ' INSERT INTO ' || v_logTableName || ' SELECT ' || v_colList1
|| ', ''DEL'', ''OLD'' FROM old_table tbl;'
|| ' ELSIF (TG_OP = ''INSERT'') THEN'
|| ' INSERT INTO ' || v_logTableName || ' SELECT ' || v_colList1
|| ', ''INS'', ''NEW'' FROM new_table tbl;'
|| ' ELSIF (TG_OP = ''UPDATE'') THEN'
|| ' FOR r IN'
|| ' WITH'
|| ' o AS (SELECT ' || v_colList1 || ', row_number() OVER
() AS ord FROM old_table tbl'
|| ' ),'
|| ' n AS (SELECT ' || v_colList1 || ', row_number() OVER
() AS ord FROM new_table tbl'
|| ' )'
|| ' SELECT ' || v_colList2
|| ' FROM o JOIN n USING(ord)'
|| ' LOOP'
|| ' INSERT INTO ' || v_logTableName
|| ' SELECT ' || v_colList3 || ', ''UPD'', ''OLD'';'
|| ' INSERT INTO ' || v_logTableName
|| ' SELECT ' || v_colList4 || ', ''UPD'', ''NEW'';'
|| ' END LOOP;'
|| ' END IF;'
|| ' RETURN NULL;'
|| 'END;'
|| '\$logfnct\$ LANGUAGE plpgsql SECURITY DEFINER;';
-- creation of the log trigger on the application table, using the
previously created log function
EXECUTE 'CREATE TRIGGER insert_log_trg'
|| ' AFTER INSERT ON ' || v_fullTableName || ' REFERENCING NEW
TABLE AS new_table'
|| ' FOR EACH STATEMENT EXECUTE PROCEDURE ' || v_logFnctName ||
'()';
EXECUTE 'CREATE TRIGGER update_log_trg'
|| ' AFTER UPDATE ON ' || v_fullTableName || ' REFERENCING OLD
TABLE AS old_table NEW TABLE AS new_table'
|| ' FOR EACH STATEMENT EXECUTE PROCEDURE ' || v_logFnctName ||
'()';
EXECUTE 'CREATE TRIGGER delete_log_trg'
|| ' AFTER DELETE ON ' || v_fullTableName || ' REFERENCING OLD
TABLE AS old_table'
|| ' FOR EACH STATEMENT EXECUTE PROCEDURE ' || v_logFnctName ||
'()';
RETURN;
END;
\$_create_tbl\$;

CREATE TABLE myTbl1 (
col11 INT NOT NULL,
col12 TEXT ,
col13 TEXT ,
PRIMARY KEY (col11)
);

CREATE TABLE myTbl3 (
col41 INT NOT NULL,
col44 INT ,
PRIMARY KEY (col41),
FOREIGN KEY (col44) REFERENCES myTbl1 (col11) ON DELETE CASCADE ON UPDATE
SET NULL
);

select create_tbl('public','mytbl1');
select create_tbl('public','mytbl3');

insert into myTbl1 select i, 'ABC', 'abc' from generate_series (1,10100) as
i;
update myTbl1 set col13=E'\\034'::bytea where col11 <= 500;
delete from myTbl1 where col11 > 10000;

*EOF*

As a result, the last DELETE statement fails. I get:

CREATE FUNCTION
CREATE TABLE
CREATE TABLE
NOTICE: table "mytbl1_log" does not exist, skipping
create_tbl
------------

(1 row)

NOTICE: table "mytbl3_log" does not exist, skipping
create_tbl
------------

(1 row)

INSERT 0 1101
UPDATE 0
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost

The postgresql.conf file has default parameters, except:
listen_addresses = '*'
port = 5410
max_prepared_transactions 5
logging_collector = on
track_functions = all
track_commit_timestamp = on

Best regards.
Philippe Beaudoin.