[Gnumed-devel] what about commit?

[syan tan]

In the case of postgres usernames, we have created "pseudo users" where
a "_" is prepended to the user name.
"user" has only read access
"_user" has write access.
That way, we prevent accidental programmatical write access to the
database.

Problem was that Postgres does not allow real concurrent atomic
transactions inside of the scope of a single client connection; if one
transaction fails, ALL started and not committed transaction within that
connection will be rolled back as well.


What does that mean " real concurrent atomic transactions inside of the 
scope of a single client connection" ?
With one connection, I didn't think you could have more than one 
transaction ( that's why the commit() operation is
on the connection object).  If  one wants more than one connection 
running concurrently, don't you just get a new
connection object?

I tried it:

>>> from pyPgSQL import PgSQL
>>> c = PgSQL.connect("localhost::gnumed")
>>> c2 = PgSQL.connect("localhost::gnumed")
>>> cu = c.cursor()
>>> cu2 = c2.cursor()
>>> cu.execute("insert into enum_comm_types(description) values('test20')")
>>> cu2.execute("insert into enum_comm_types(description) 
values('test21')")
>>> cu.rollback()
Traceback (most recent call last):
 File "<stdin>", line 1, in ?
AttributeError: Cursor instance has no attribute 'rollback'
>>> c.rollback()
>>> c2.commit()
>>> cu.execute("select * from enum_comm_types")
>>> print cu.fetchall()
[[1, 'hello'], [2, 'world'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 
'TEST'], [7, 'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 
'TEST7'], [15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], 
[23, 'test21']]
>>> cu.execute("insert into enum_comm_types(description) values('test20')")
>>> cu2.execute("insert into enum_comm_types(description) 
values('test22')")
>>> cu2.rollback()
Traceback (most recent call last):
 File "<stdin>", line 1, in ?
AttributeError: Cursor instance has no attribute 'rollback'
>>> c2.rollback()
>>> c.commit()
>>> cu.execute("select * from enum_comm_types")
>>> print cu.fetchall()
[[1, 'hello'], [2, 'world'], [3, 'This'], [4, 'Is'], [5, 'A'], [6, 
'TEST'], [7, 'TEST2'], [8, 'TEST3'], [11, 'TEST4'], [13, 'TEST6'], [14, 
'TEST7'], [15, 'TEST9'], [16, 'TEST10'], [17, 'TEST11'], [18, 'TEST12'], 
[23, 'test21'], [24, 'test20']]
>>>

The first time, the second transaction was able to commit it's change. 
The second time , the first transaction was able to commit.
The semantics is that one connection object per transaction : otherwise 
you have to start labelling the transactions per connection ;
I didn't know there was packages that allowed multiple concurrent 
tranasactions per session.