[BUGS] BUG #14808: V10-beta4, backend abort
От | phb07@apra.asso.fr |
---|---|
Тема | [BUGS] BUG #14808: V10-beta4, backend abort |
Дата | |
Msg-id | 20170909064853.25630.12825@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: [BUGS] BUG #14808: V10-beta4, backend abort
(Michael Paquier <michael.paquier@gmail.com>)
|
Список | 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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: John R PierceДата:
Сообщение: Re: [BUGS] Query with "LIMIT 1" 10x slower than without LIMIT
Следующее
От: matthew.r.maurer@gmail.comДата:
Сообщение: [BUGS] BUG #14809: Heap Corruption with deeply nested triggers.