[Zope] SQL transaction uncommited?

[Vlada Macek]

[At 17.12.2005 15:17, Tino Wildenhain kindly sent the following quotation.]

>>In the meanwhile I also discovered that when I put my 'insert into
>>testtable...' to ZSQL method, the behavior is the same -- psql does not
>>see new row, but the sequence is incremented. Now it's strange, isn't?
>>    
>>
>Actually not. sequences are incremented under any circumstances.
>Thats per definition.
>  
>
By strange I meant the behaviour of ZSQL method is the same as my
product's. The seq incrementation is of course good thing. :-)

>  
>
>>With each ZSQL method call I now found out that a new connection to the
>>database is keeping opened and RowExclusiveLock for testtable and
>>AccessShareLock for its sequence is pending. In now have tens of
>>connections and locks from my tryings. I may did some error in
>>configuration, but I have no clue where and which.
>>
>>Python 2.3.5, Zope 2.7.5-final, psycopg2-2.0b6, PostgreSQL 8.1.
>>    
>>
>
>If all goes well and no exception bubbles up to the
>zpublisher, it commits that transaction - both on zope and
>the database. In your case something seems to prevent that,
>this could be an error from one of your queries which you
>swallow in some try:/except: somewhere?
>
>Maybe you can lay out a simple test case where this happens.
>  
>
After a further investigation, the test case is fairly narrow now:

I create a ZPsycopgDA (2.0b6) connection object (txn mode SERIALIZABLE).
I create a ZSQL method bound to this connection and containing "insert
into testtable (n) values 1". When I use the Test tab to execute the
method, command executes ok ("This statement returned no results."). And
the statement log of PostgreSQL shows:

Dec 17 15:22:09 postgres[19407]: [2-1] LOG:  connection received:
host=127.0.0.1 port=36685
Dec 17 15:22:09 postgres[19407]: [3-1] LOG:  connection authorized:
user=tuttle database=his
Dec 17 15:22:09 postgres[19407]: [4-1] LOG:  statement: SET DATESTYLE TO
'ISO'
Dec 17 15:22:09 postgres[19407]: [5-1] LOG:  statement: SHOW client_encoding
Dec 17 15:22:09 postgres[19407]: [6-1] LOG:  statement: SHOW
default_transaction_isolation
Dec 17 15:22:09 postgres[19407]: [7-1] LOG:  statement: BEGIN; SET
TRANSACTION ISOLATION LEVEL SERIALIZABLE
Dec 17 15:22:09 postgres[19407]: [8-1] LOG:  statement: insert into
testtable (n) values (1)

Dec 17 15:22:09 postgres[19408]: [2-1] LOG:  connection received:
host=127.0.0.1 port=36686
Dec 17 15:22:09 postgres[19408]: [3-1] LOG:  connection authorized:
user=tuttle database=his
Dec 17 15:22:09 postgres[19408]: [4-1] LOG:  statement: SET DATESTYLE TO
'ISO'
Dec 17 15:22:09 postgres[19408]: [5-1] LOG:  statement: SHOW client_encoding
Dec 17 15:22:09 postgres[19408]: [6-1] LOG:  statement: SHOW
default_transaction_isolation

Dec 17 15:22:09 postgres[19409]: [2-1] LOG:  connection received:
host=127.0.0.1 port=36687
Dec 17 15:22:09 postgres[19409]: [3-1] LOG:  connection authorized:
user=tuttle database=his
Dec 17 15:22:10 postgres[19409]: [4-1] LOG:  statement: SET DATESTYLE TO
'ISO'
Dec 17 15:22:10 postgres[19409]: [5-1] LOG:  statement: SHOW client_encoding
Dec 17 15:22:10 postgres[19409]: [6-1] LOG:  statement: SHOW
default_transaction_isolation

So the DA opens three connections forcing postgres to spawn new
processes. I don't know why. These connections are pending there.

What is serious here is the first connection with my statement is not
committed. I think this is a bug, probably in ZPsycopgDA.

Is there some other DA for PostgreSQL which is known to be stable and
with txns working properly? Should I try the psycopg1?

Thanks.

-- 

\//\/\
(Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.)