From: | Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com> |
---|---|
To: | Kevin Grittner <kgrittn(at)gmail(dot)com> |
Cc: | Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Craig Ringer <craig(at)2ndquadrant(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, David Fetter <david(at)fetter(dot)org>, Kevin Grittner <kgrittn(at)ymail(dot)com>, Amit Khandekar <amit(dot)khandekar(at)enterprisedb(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Corey Huinker <corey(dot)huinker(at)gmail(dot)com> |
Subject: | Re: delta relations in AFTER triggers |
Date: | 2017-05-04 09:12:59 |
Message-ID: | CANEvxPoOodsb_ZJwgSOLpYic5s4-0pZOJWrWnRU-TpgV9KJQdA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(dot)huinker(at)gmail(dot)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?
>
>
>
Attachment | Content-Type | Size |
---|---|---|
log.zip | application/zip | 776.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2017-05-04 09:37:36 | Re: CTE inlining |
Previous Message | Thomas Munro | 2017-05-04 08:46:07 | Re: transition table behavior with inheritance appears broken (was: Declarative partitioning - another take) |