Re: delta relations in AFTER triggers

Hi,

I have been testing this for a while and observed a server crash while
referencing table column value in a trigger procedure for AFTER DELETE
trigger.

-- Steps to reproduce:
CREATE TABLE t1(c1 int);
CREATE TABLE t2(cc1 int);
INSERT INTO t1 VALUES (10);
INSERT INTO t2 VALUES (10);

CREATE OR REPLACE FUNCTION trig_func() RETURNS trigger AS
$$ BEGIN
    DELETE FROM t1 WHERE c1 IN (select OLD.cc1 from my_old);
    RETURN OLD;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER trg1
  AFTER DELETE ON t2
    REFERENCING OLD TABLE AS my_old
    FOR EACH ROW
  EXECUTE PROCEDURE trig_func();

DELETE FROM t2 WHERE cc1 =10;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

On further testing it without delta relations in AFTER trigger,it executed
successfully.

CREATE OR REPLACE FUNCTION trig_func() RETURNS trigger AS
$$ BEGIN
    DELETE FROM t1 WHERE c1 IN (select OLD.cc1 from t2);
    RETURN OLD;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER trg1
  AFTER DELETE ON t2
    FOR EACH ROW
  EXECUTE PROCEDURE trig_func();

DELETE FROM t2 WHERE cc1 =10;
DELETE 1
postgres=# select * from t1;
 c1
----
 10
(1 row)

postgres=# select * from t2;
 cc1
-----
(0 rows)

Logfile and core dump attached for reference.


*Thanks & Regards,*

*Prabhat Kumar Sahu*
Mob: 7758988455
Skype ID: prabhat.sahu1984

www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

On Thu, Apr 13, 2017 at 8:29 AM, Corey Huinker <corey.huinker@gmail.com>
wrote:

> Great.  Thanks.  I wonder if there is some way we can automatically
>> include code fragments in the documentation without keeping them in
>> sync manually.
>>
>>
> In whatever extra docs you add, could you include an example of an INSERT
> ON CONFLICT, and potentially a CTE query that does two operations on the
> same table. I'm not clear on what to expect when a statement does a mix of
> INSERT, UPDATE, and DELETE? Will there be multiple firings of the trigger
> in a single statement, or will the before/after sets be mashed together
> regardless of which part of the query generated it?
>
>
>