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@apra.asso.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.