Re: Portals and nested transactions

Tom Lane wrote:

> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
>> 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.
>
>> So within this proposal, a query executed by normal means will get its
>> resources saved in the transaction ResourceOwner?
>
> No, *all* queries are executed within portals. The reason we need a
> transaction ResourceOwner is because query parsing/planning happens in
> advance of creating the portal, so we need someplace to keep track of
> resources acquired during that process.
>
>> How is the "unnamed portal" affected by it?
>
> Same as the rest.
>
> I don't recall whether SPI creates actual portals, but we'd definitely
> want it to create a new ResourceOwner for queries it runs.
>
>> 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).
>
> This is certainly possible. Whether it's a good idea needs further
> discussion...

I didn't want to be the first to speak up on this as I'm relatively new to
the group (so thank you Alvaro), but I would definitely perfer the option
of either trans or non-trans behavior. I can see using the non-trans
behavior in a cursor based FOR loop with a savepoint/subtrans allowing me
to fail on row x and continue on to row x+1 immediately. Then, after
choosing trans-mode, I could implement a multi-strategy row processor.

Of course, just to be difficult, my ideal default would be:

Q1 -- Portals close
Q2 -- Portals do NOT roll back to previous state.

However, I do see the logical inconsistency in that. But then again,
subtransactions/savepoints are not ACID, so it seems to be implementation
dependent.

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
--miker