Re: delta relations in AFTER triggers

On Thu, May 04, 2017 at 09:51:03PM +1200, Thomas Munro wrote:
> On Thu, May 4, 2017 at 9:12 PM, Prabhat Sahu
> <prabhat(dot)sahu(at)enterprisedb(dot)com> wrote:
> > 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.
>
> Reproduced here. The stack looks like this:
>
> frame #3: 0x000000010f06f8b0
> postgres`ExceptionalCondition(conditionName="!(readptr->eflags &
> 0x0002)", errorType="FailedAssertion", fileName="tuplestore.c",
> lineNumber=1237) + 128 at assert.c:54
> frame #4: 0x000000010f0cbc85
> postgres`tuplestore_rescan(state=0x00007ff219840200) + 85 at
> tuplestore.c:1237
> frame #5: 0x000000010eced9b1
> postgres`ExecReScanNamedTuplestoreScan(node=0x00007ff21d007840) + 81
> at nodeNamedtuplestorescan.c:197
> frame #6: 0x000000010eca46a6
> postgres`ExecReScan(node=0x00007ff21d007840) + 822 at execAmi.c:216
> frame #7: 0x000000010ece7eca
> postgres`ExecNestLoop(node=0x00007ff21d006310) + 538 at
> nodeNestloop.c:148
>
> I think the problem is that the tuplestore read pointer hasn't been
> opened with the "rewindable" flag. It works for me with the attached.

[Action required within three days. This is a generic notification.]

The above-described topic is currently a PostgreSQL 10 open item. Kevin,
since you committed the patch believed to have created it, you own this open
item. If some other commit is more relevant or if this does not belong as a
v10 open item, please let us know. Otherwise, please observe the policy on
open item ownership[1] and send a status update within three calendar days of
this message. Include a date for your subsequent status update. Testers may
discover new open items at any time, and I want to plan to get them all fixed
well in advance of shipping v10. Consequently, I will appreciate your efforts
toward speedy resolution. Thanks.

[1] https://www.postgresql.org/message-id/20170404140717.GA2675809%40tornado.leadboat.com