[Zope] SQL transaction uncommited?

[Vlada Macek]

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