Re: [Zope] SQL transaction uncommited?
[At 19.12.2005 01:39, Gabriel Genellina kindly sent the following quotation.] Gabriel, I appreciate you also reviewed my code. >> def manage_afterAdd(self, item, container): if item is self: >> self.db = getattr(self,MY_CONNECTION_ID)._v_database_connection > > Keeping a _v_ attribute is *not* a good idea - it's suposed to be > *volatile*. Later I moved the connection obj lookup to qry() method, so the volatility of the attribute is not an issue now. I also learnt from Shared/DC/ZRDB/Connection.py what you were talking about and changed "._v_database_connection" to "()", which definitely looks cleaner. :-) >> def qry(self, query): results = self.db.query(query) recs = { >> 'names': Results(results).names(), 'tuples': >> Results(results).tuples() } return recs > > Why create a Results object twice? results = > Results(self.db.query(query))... I also fixed this overlook later, thanks. > I don't know how the ProgressSQL adapter is implemented, but *if* > every query() call created a different connection or started a > different transaction -which does not complete until the Zope > transaction commits finally- *then* you would see those results. > Updates made by the *same* transaction should always be available to > that transaction, whatever the trans. isolation level in use. If this > was the case, that may indicate that the Progress adapter is not > properly written. On psycopg mailing list I was informed that my problem is a known issue of version 2.0b6 and that I should wait for b7 or take SVN head. People, do not trust everything you are told on IRC. Sometimes it's better to stay with stable releases, psycopg 1.1.18 works great and as expected. :-) -- \//\/\ (Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.) ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
At Saturday 17/12/2005 09:31, Vlada Macek wrote: in ZopeBook I read that Zope's transaction are tied to SQL backend's ones. I do not know whether this applies to ZSQL methods only, but I do not expect it. I got this table: def manage_afterAdd(self, item, container): if item is self: self.db = getattr(self,MY_CONNECTION_ID)._v_database_connection Keeping a _v_ attribute is *not* a good idea - it's suposed to be *volatile*. It's not clear from this partial fragment, but if self inherits from Connection, use __call__ instead -i.e. place a () after the object- to obtain its database connection (using __call__ ensures that the connection is open). def qry(self, query): results = self.db.query(query) recs = { 'names': Results(results).names(), 'tuples': Results(results).tuples() } return recs Why create a Results object twice? results = Results(self.db.query(query))... def test0(self): self.qry('insert into testtable (n) values (10)') def test1(self): self.qry('insert into testtable (n) values (10)') self.db.commit() def test2(self): self.qry('insert into testtable (n) values (10)') self.qry('commit') def test3(self): self.qry('insert into testtable (n) values (10)') return self.qry('select * from testtable') def test4(self): self.qry('insert into testtable (n) values (10); commit') def test5(self): self.qry('insert into testtable (n) values (10); commit') return self.qry('select * from testtable') When I call test0,1,2,3 -- the inserted row is not visible in psql, but the sequence is incremented! I'm afraid there is some pending transaction I cannot reach at that moment anymore. The test4,5 properly add the row to table (and increment the seq of course) as I can watch via psql immediatelly. In test3 the returned result does not contain the new row, in test5 the new row is contained in select. I don't know how the ProgressSQL adapter is implemented, but *if* every query() call created a different connection or started a different transaction -which does not complete until the Zope transaction commits finally- *then* you would see those results. Updates made by the *same* transaction should always be available to that transaction, whatever the trans. isolation level in use. If this was the case, that may indicate that the Progress adapter is not properly written. What am I doing wrong and how must I arrange it not need to care about transactions at all? I expected the SQL transactions are transparent in Zope and they are either commited when the webbrowser gets the no-exception response and rolled back otherwise. I've only used the ZODBCDA adapter and it does not show these phenomena; its SQL transactions *are* coupled to the Zope transaction (I learned that the hard way, in a corner case...) Gabriel Genellina Softlab SRL ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
[At 17.12.2005 15:37, Vlada Macek kindly sent the following quotation.] >What is serious here is the first connection with my statement is not >committed. I think this is a bug, probably in ZPsycopgDA. > > Downgrading psycopg to 1.1.18 solved my problem with pending transactions. I'm going to post a report to the psycopg mailing list. -- \//\/\ (Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.) ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
[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.) ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
Am Samstag, den 17.12.2005, 14:20 +0100 schrieb Vlada Macek: > [At 17.12.2005 13:49, Andreas Jung kindly sent the following quotation.] > > > Dealing with transaction is the task of the database adapter and not > > the ZSQL methods. If you need to deal with transactions yourself you > > must subclass the transaction manager TM and implement the hooks for > > commit and abort (see TM.py). > > Yes, I looked at TM.py. I should have mentioned that I use ZPsycopgDA > and ZPsycopgDA/db.py is subclassing and using TM already. So I was > expecting the _finish() and _abort() are bound to PostgreSQL in the SQL > connection I use. > > 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. > 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. I wonder how you actually call it in context of zope. Usually with the first ZSQL method you execute your connection to the database takes part of zopes transaction machinery. 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. Regards Tino ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
[At 17.12.2005 13:49, Andreas Jung kindly sent the following quotation.] > Dealing with transaction is the task of the database adapter and not > the ZSQL methods. If you need to deal with transactions yourself you > must subclass the transaction manager TM and implement the hooks for > commit and abort (see TM.py). Yes, I looked at TM.py. I should have mentioned that I use ZPsycopgDA and ZPsycopgDA/db.py is subclassing and using TM already. So I was expecting the _finish() and _abort() are bound to PostgreSQL in the SQL connection I use. 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? 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. -- \//\/\ (Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.) ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
Forwarded Message Date: 17. Dezember 2005 13:31:23 +0100 From: Vlada Macek <[EMAIL PROTECTED]> To: zope@zope.org Subject: [Zope] SQL transaction uncommited? Hi, in ZopeBook I read that Zope's transaction are tied to SQL backend's ones. I do not know whether this applies to ZSQL methods only, but I do not expect it. I got this table: Dealing with transaction is the task of the database adapter and not the ZSQL methods. If you need to deal with transactions yourself you must subclass the transaction manager TM and implement the hooks for commit and abort (see TM.py). -aj pgpnBYS5rqhbu.pgp Description: PGP signature ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
[Zope] SQL transaction uncommited?
Hi, in ZopeBook I read that Zope's transaction are tied to SQL backend's ones. I do not know whether this applies to ZSQL methods only, but I do not expect it. I got this table: CREATE TABLE testtable ( id serial, -- uses its own sequence n int4 ); I use PostgreSQL and I have psql (an interactive SQL processor) opened and I'm watching the testtable and its primary key sequence object. I'm writing a product tool and I test the SQL connection with the following code: def manage_afterAdd(self, item, container): if item is self: self.db = getattr(self,MY_CONNECTION_ID)._v_database_connection def qry(self, query): results = self.db.query(query) recs = { 'names': Results(results).names(), 'tuples': Results(results).tuples() } return recs def test0(self): self.qry('insert into testtable (n) values (10)') def test1(self): self.qry('insert into testtable (n) values (10)') self.db.commit() def test2(self): self.qry('insert into testtable (n) values (10)') self.qry('commit') def test3(self): self.qry('insert into testtable (n) values (10)') return self.qry('select * from testtable') def test4(self): self.qry('insert into testtable (n) values (10); commit') def test5(self): self.qry('insert into testtable (n) values (10); commit') return self.qry('select * from testtable') When I call test0,1,2,3 -- the inserted row is not visible in psql, but the sequence is incremented! I'm afraid there is some pending transaction I cannot reach at that moment anymore. The test4,5 properly add the row to table (and increment the seq of course) as I can watch via psql immediatelly. In test3 the returned result does not contain the new row, in test5 the new row is contained in select. What am I doing wrong and how must I arrange it not need to care about transactions at all? I expected the SQL transactions are transparent in Zope and they are either commited when the webbrowser gets the no-exception response and rolled back otherwise. Thanks in advance! -- \//\/\ (Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.) ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )