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

[mateusz at loskot.net]

Paul,

I understand FDO requirements similarly as Orest explained,
also I understand how PostgreSQL tranactions work (not LT).
But still, I don't understand how to workaround the limitation of
unsupported nested transactions.

I begin new transaction many times for SELECT query that uses declared 
cursor.
Next, when I want to BEGIN next transaction ie. for update/instert,
I get warning because there is already transaction opened for the 
cursor, let's say outermost one.

I can not require that all DML commands will run only
between SELECT cursors and not when one of cursor is open.

That's why I decided to use soft transactions.

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.

How to solve it?

Mat


Paul Ramsey wrote:
> In that case, PostgreSQL has all the facility needed to support the FDO.
> 
> P
> 
> Orest Halustchak wrote:
>> I don't think that save points would work the way we'd expect a regular
>> transaction to work. A save point is within a transaction and while you
>> can roll back to a save point, you can't really commit at a save point
>> except by committing the transaction that includes it.
>>
>> Again, we're talking about short transactions in this case, not long
>> transactions.
>>
>> Basically, fdo expects the standard short transaction mechanism. It's
>> not a nested transaction concept. You can execute any set of data update
>> operations and they will be isolated from other users until a
>> transaction commit. At that time, the edits are permanent and other
>> users can see them. A rollback will remove all edits done since the
>> beginning of the transaction. A short transaction supports ACID
>> properties (atomicity, consistency, isolation, and durability).
>>
>> Thanks,
>> Orest.
>>
>> -----Original Message-----
>> From: fdo-internals-bounces at lists.osgeo.org
>> [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Paul Ramsey
>> Sent: Tuesday, May 08, 2007 12:38 PM
>> To: FDO Internals Mail List
>> Subject: Re: [fdo-internals] Motion: RFC 3 - PostGIS provider
>>
>> Jason Birch wrote:
>>
>>> PostgreSQL doesn't currently support nested transactions.  Mateusz has
>>
>>> been using "soft" transactions internally to ensure per-action 
>>> consistency, so if we support explicit transactions he will have to 
>>> somehow test for existing transaction before starting internal 
>>> transactions.
>>
>> Not knowing how FDO is actually exercising the database or what contract
>> it expects, I will blindly ask: is it possible that savepoints do what
>> FDO needs done?
>>
>> http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html
> 
> 


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