Re: Portals and nested transactions

On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote:
> I've been thinking about what to do with cursors in subtransactions.
> The problem really includes both cursors (created with DECLARE CURSOR)
> and portals (created with the V3-protocol Bind message) since they are
> the same kind of animal internally, namely a Portal.

So within this proposal, a query executed by normal means will get its
resources saved in the transaction ResourceOwner? How is the "unnamed
portal" affected by it? Supposing that the unnamed portal is treated
like any other portal (with its own ResourceOwner), we have to make sure
to shut it down properly if something "goes wrong". Not sure how this
applies to portals created by SPI.

> Q1: Should Portals successfully created within the failed subxact
> be closed? Or should they remain open?
>
> Q2: If the subxact changed the state of a pre-existing Portal, should
> that state change roll back? In particular, can a Close Portal
> operation roll back?

IMHO the transactional view is better; if we take the other approach,
then users can't just use a simple "retry loop" around a subtransaction.

> The discussion sort of trailed off there because we had no ideas how to
> implement either. I will now sketch some implementation ideas about how
> to do the nontransactional way.

Sounds excellent to me.

> We could support the transactional behavior as well, but not very
> efficiently (at least not in the first cut).

I think we should decide what behavior is best now, and not change it in
a later release. If it's going to be somewhat inefficient, try to
minimise it. But just as I decided not to support the "nested
transaction" syntax and instead change to the savepoint syntax, lets
keep things consistent. IMHO anyway.

On the other hand, some people supported the idea that v3 Bind portals
should behave nontransactionally, while DECLARE portals should behave
transactionally. Maybe we could make that a property of the portal, or
even a user-selectable property (where we would define a reasonable
default behavior).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In a specialized industrial society, it would be a disaster
to have kids running around loose." (Paul Graham)