[fdo-internals] Motion: RFC 3 - PostGIS provider

[mateusz at loskot.net]

Paul Ramsey wrote:
> mateusz at loskot.net wrote:
> 
>> For example, how to solve following situation with distinct PgSQL 
>> transactions:
>>
>> - BEGIN
>> -- DECLARE CURSOR xxx SELECT ...
>> --- do something with select result
>> ---- BEGIN
>> ----- INSERT INTO ...
>> ---- COMMIT
>> -- CLOSE xxx
>> - COMMIT
>>
>> In steps presented above I am not able to call the second
>> BEGIN because there is transaction started already.
> 
> Is the internal BEGIN/COMMIT for the INSERT really needed? Two alternate 
> paths, why don't these work:
> 
>  > - BEGIN
>  > -- DECLARE CURSOR xxx SELECT ...
>  > --- do something with select result
>  > -- CLOSE xxx
>  > - COMMIT
>  > - BEGIN
>  > -- INSERT INTO ...
>  > - COMMIT
> 
> Or,
> 
>  > - BEGIN
>  > -- DECLARE CURSOR xxx SELECT ...
>  > --- do something with select result
>  > -- INSERT INTO ...
>  > -- CLOSE xxx
>  > - COMMIT

Paul,

Both versions will work with current implementation.
But my example mimics steps taken by a user, but not what I call inside
fdopostgis provider.

In other words,

1. If a user will begin-and-commit/rollback transaction
as subsequent steps, then it will work as expected

2. if a user will begin new transaction and next he
will begin one more transaction, without closing the one previously 
started, then we get nested transactions and I can not deal with it as
ie. MySQL does, but I have to use first-hard-and-next-soft transactions.

This strategy is equivalent to the second case in your explanation:

- BEGIN - regular transaction
-- SQL command
-- BEGIN SOFT TRANS. - user thinks it's a regular BEGIN, but it can't be
--- SQL command
-- COMMIT SOFT TRANS.
- COMMIT


And here I don't know how could I replace BEGIN/COMMIT SOFT
with real transactions.
It seems to be out of my control what the provider's client will do,
and in what order.
Certainly, my understanding may be incorrect.

BTW, soft transaction works as a reference counter to the common block 
of real transaction.
So, it's possible to do the "hard rollback" from inner soft transaction,
what will unwind all soft transactions and call ROLLBACK on the
outermost real transaction.


Cheers
-- 
Mateusz Loskot
http://mateusz.loskot.net