Re: BUG #1592: "with hold" cursor problem

Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
> test=# create function crashme() returns text as 'select timeofday()'
> test-# language sql stable;
> CREATE FUNCTION
> test=# begin;
> BEGIN
> test=# declare t cursor with hold for select crashme() as x from pg_class;
> DECLARE CURSOR
> test=# commit;
> server closed the connection unexpectedly

You don't even need the begin/commit ...

The immediate problem is that PersistHoldablePortal() forgot to
establish the portal's snapshot as ActiveSnapshot while running the
executor to collect the rest of the cursor data. This is trivial
to fix, but after fixing that, it crashes elsewhere:

#4 0x2cf01c in ExceptionalCondition (
conditionName=0xa2998 "!(afterTriggers != ((void *)0))",
errorType=0xa21dc "FailedAssertion", fileName=0xa1fa4 "trigger.c",
lineNumber=2370) at assert.c:51
#5 0x18fe9c in AfterTriggerBeginQuery () at trigger.c:2370
#6 0x1aee04 in postquel_start (es=0x4010f310, fcache=0x4010ed28)
at functions.c:332
#7 0x1af168 in postquel_execute (es=0x7b02fed0, fcinfo=0x7f,
fcache=0x7b02fec8, resultcontext=0x4006b0d8) at functions.c:466
#8 0x1af3b8 in fmgr_sql (fcinfo=0x7b03c1c0) at functions.c:639
...
#17 0x1a3994 in ExecutorRun (queryDesc=0x4010d188,
direction=ForwardScanDirection, count=0) at execMain.c:228
#18 0x17b0a0 in PersistHoldablePortal (portal=0x400c40a0) at portalcmds.c:379
#19 0x2eb968 in AtCommit_Portals () at portalmem.c:473
#20 0x10dc5c in CommitTransaction () at xact.c:1452
#21 0x10e37c in CommitTransactionCommand () at xact.c:1941
#22 0x2480d4 in finish_xact_command () at postgres.c:1844

The problem here is that CommitTransaction shuts down the trigger
manager before shutting down portals, so of course trigger.c barfs
when the SQL function wants it to check for queued triggers.

There seems to be a rather fundamental ordering problem here,
since it's certainly possible for a holdable portal to contain
volatile functions that must fire triggers, and yet I suppose that
a trigger might also want to use or create cursors. Perhaps we
have to iterate "close holdable cursors" and "fire triggers" until
there's nothing more to do. Thoughts anyone?

regards, tom lane