While investigating Merlin's bug report here:
http://archives.postgresql.org/pgsql-general/2006-10/msg00447.php
I realized that we've completely failed to consider the interactions
of $subject. In particular, functions.c still thinks that SELECT is
the only type of query that can return rows.
ISTM that ideally, a query with RETURNING ought to act like a SELECT
for the purposes of a SQL function --- to wit, that the result rows are
discarded if it's not the last query in the function, and are returned
as the function result if it is.
The difficulty with this is that unlike SELECT, a query with RETURNING
might be queueing up AFTER triggers, which we shouldn't fire until the
query is fully executed.
Merlin's report shows that we've already got a problem in the back
branches with mishandling of after-trigger state, because we push an
AfterTrigger stack level at start of an SQL function command, and then
are willing to return from the function with that stack level still
active if it's a set-returning function. I think we can fix this in
the back branches by the expedient of not pushing a stack level (ie,
not calling AfterTriggerBegin/EndQuery) unless it's a non-SELECT
command --- SELECT will never queue triggers, and we never return
partway through a non-SELECT command. But this falls down for
RETURNING queries.
I thought about fixing this by extending the AfterTrigger state
structure to let it be a tree rather than just a stack, ie, we could
temporarily pop the function AfterTrigger status entry without executing
any queued triggers, and then push it back on when re-entering the
function. This seems horribly messy however, and I'm not sure we could
still promise unsurprising order of trigger execution in complicated
cases.
I think the most promising answer may be to push RETURNING rows into a
TupleStore and then read them out from there, which is pretty much the
same approach we adopted for RETURNING queries inside portals. This'd
allow the query to be executed completely, and its triggers fired,
before we return from the SQL function.
Comments?
regards, tom lane
