Re: Portals and nested transactions

Mike Rylander wrote:

> 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.
>

To make that a little more specific, something along the lines of:

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
[ IN { LEXICAL | GLOBAL } SCOPE
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

... or some such... I think in perl. :)

>>
>> 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