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