Re: Portals and nested transactions

Mike Rylander wrote:

> Tom Lane wrote:
> 
>> Alvaro Herrera <alvherre@dcc.uchile.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@postgresql.org)
> 

-- 
--miker