From: phb07@apra.asso.fr
To: pgsql-bugs@postgresql.org
Cc: phb07@apra.asso.fr
Subject: BUG #14808: V10-beta4, backend abort
Date: Sat, 09 Sep 2017 06:48:53 +0000
Message-ID: <20170909064853.25630.12825@wrigleys.postgresql.org> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
|| ' 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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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: phb07@apra.asso.fr
Subject: Re: BUG #14808: V10-beta4, backend abort
In-Reply-To: <20170909064853.25630.12825@wrigleys.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