[BUGS] BUG #14808: V10-beta4, backend abort - Mailing list pgsql-bugs

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        || '
ADDCOLUMN 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
nUSING(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)
REFERENCESmyTbl1 (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, skippingcreate_tbl 
------------
(1 row)

NOTICE:  table "mytbl3_log" does not exist, skippingcreate_tbl 
------------
(1 row)

INSERT 0 1101
UPDATE 0
server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing
therequest.
 
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.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs