Re: delta relations in AFTER triggers

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.

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