Re: delta relations in AFTER triggers

On Thu, May 4, 2017 at 9:12 PM, Prabhat Sahu
<prabhat.sahu@enterprisedb.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.

-- 
Thomas Munro
http://www.enterprisedb.com