Re: Assertion failure with a subtransaction and cursor

I wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Hmm, I think we should track temporary files using resource owners.

> That would probably be a workable solution if temp files are the only
> problem. What I'm afraid of is that this type of problem exists
> *everywhere* that we track the need for cleanup operations using the
> assumption that subtransactions are nested.

I just spent a tedious hour digging through every function called by
AbortSubTransaction(), and came to the conclusion that this fear may be
overblown, so long as we are willing to stipulate that user-visible
side effects caused by a cursor's query are to be rolled back if they
occur during a subtransaction that is rolled back. From the user's
perspective this may make things a bit unpredictable, since it is not
always clear exactly when a side effect will occur during the execution
of a query. However it doesn't seem like it can actually break the
system.

(At least not for code in core CVS. Outside modules might be doing
unsafe things in RegisterSubXactCallback callbacks. But that's not
our responsibility to fix.)

It might be a good idea to forbid writeable CTEs in cursor queries,
because that would expose the unpredictability a lot more, and maybe
open some internal issues too -- I noted snapshot management as a likely
problem if the executor is allowed to create its own snapshots. But as
far as what's in CVS is concerned that's not an issue yet anyhow. If
we did want to allow it, we could probably make it safe by forcing all
the writable CTEs to run at cursor creation time.

So as far as I can tell at the moment, temp files really are the only
problem, and making them be managed by resource owners instead of a
subxact-based release policy should fix that. I can go work on that,
unless you wanted to?

regards, tom lane