postgres autocommit
Randall Smith
connection when it detects an INSERT, UPDATE, DELETE, CREATE, etc. This
is it's "autocommit" mode. The relevant code is:
if context.should_autocommit and not self.in_transaction():
self._commit_impl()
and
def _do_commit(self):
self.connection._commit_impl()
The DBAPI specifies that a connection by default begins a transaction.
So if you only issue SELECT statements you remain in a single
transaction and with a db like Postgresql, by design, you don't see
changes made to the database by other sessions. You can test this by
connecting with psycopg2, issuing a select and then ps ax | grep
postgres. You'll see "idle in transaction".
There are two problems I see with this.
1. The session's view of the database is inconsistent, depending on if
and when queries that modify records are used. Every time a commit is
issued the view of the database (in terms of MVCC) has changed.
2. In the case of Postgresql, a connection held open for a long time,
running only SELECT queries, would never see updates from other sessions
and this might tax the database while it tries to maintain MVCC for that
connection.
Once solution, at least for the psycopg2 based connection would be to
call commit on SELECTS also when not using a transaction (SA
transaction). I've done some testing and this seems to work well.
I know that to some extent this comes down to opinion on how the
connection should behave.
-Randall
Jon Nelson
(aren't supposed to) update data, like (most) select statements.
That's even cheaper than calling commit usually.
Jon
Randall Smith
> An alternate solution is to use begin.... rollback for statements that
> (aren't supposed to) update data, like (most) select statements.
> That's even cheaper than calling commit usually.
There is no begin method in the DBAPI. You could call rollback instead
of commit, but I just thought that commit made more sense in
auto"commit" mode.
-Randall
Michael Bayer
On Jan 9, 2009, at 6:05 PM, Randall Smith wrote:
>
> There are two problems I see with this.
>
> 1. The session's view of the database is inconsistent, depending on if
> and when queries that modify records are used. Every time a commit is
> issued the view of the database (in terms of MVCC) has changed.
for this reason the Session in modern SQLA defaults to
"autocommit=False", where there is in fact a transaction begun on a
single connection which remains open until commit() is called
explicitly. That way you can be assured that the state of objects
within the session is in sync with the current transactional view.
After a rollback() or commit() the full contents of the session are
expired.
But as far as the "autocommit" Session, its quite usable as well and
works just fine for web applications. The connection is returned to
the pool after each individual execute() call, no matter if its a
SELECT or a write operation, where a ROLLBACK is issued (see pool.py
line 277). "autocommit" mode is actually very common in web
applications, I believe its the default behavior for django and most
PHP applications.
> 2. In the case of Postgresql, a connection held open for a long time,
> running only SELECT queries, would never see updates from other
> sessions
> and this might tax the database while it tries to maintain MVCC for
> that
> connection.
A ROLLBACK is issued on the connection when it's returned to the pool,
and the connection is returned to the pool immediately after each
execution in the case of an autocommit Session or connectionless
execution with an engine. So in the majority of usages (that is,
using an autocommit Session or using connectionless execution, *not*
the case of explicit execution with a Connection in autocommit mode),
the connection is always completely fresh from a transactional
standpoint and no transactional state builds up. If OTOH you're doing
explciit transactions, then its up to you to decide how long you'd
like to keep your transactions opened.
> Once solution, at least for the psycopg2 based connection would be to
> call commit on SELECTS also when not using a transaction (SA
> transaction). I've done some testing and this seems to work well.
our calling of ROLLBACK when connections are returned to the pool are
equivalent to this from a "transactional state" standpoint (since the
COMMIT in your scenario is not committing anything).
I think with your project specifically, that of reading lots of
information during a reflection process given a single Connection, we
probably want to look into establishing transactional boundaries for
the reflection process, because in that case you do have the less
common use case of an explicit Connection object that's in autocommit
mode. That's something we can look into in 0.6.
Randall Smith
> I think with your project specifically, that of reading lots of
> information during a reflection process given a single Connection, we
> probably want to look into establishing transactional boundaries for
> the reflection process, because in that case you do have the less
> common use case of an explicit Connection object that's in autocommit
> mode. That's something we can look into in 0.6.
>
After thinking about it some more, I think that the main issue is a lack
of auto-commit in the DBAPI. Some drivers implement it and some don't.
Though it's usually a good idea to use transactions, there are some
times for real auto-commit.
For example, I'm working on a thick client query tool in which a
connection might be held open for days. There is an auto-commit mode as
well as a transaction mode. In auto-commit mode, changes in the
database from any session should be visible immediately.
The issue is really just when using SA as a high level DBAPI. Because
transactions require explicit calls (e.g. trans = conn.begin()), I think
that most people would imply that no transaction is in effect without
this explicit call and would be surprised to discover there is one.
Maybe you didn't intend for it to be used like I'm using it, but I must
say that for the most part, it does a great job of creating a standard
interface for the various drivers. Also, I like ResultProxy,
reflecttable and other goodies.
Curious. Why would you choose not to use the driver's autocommit
functionality if available? For example, with psycopg2, you could do:
con.set_isolation_level(0)
--Randall
Michael Bayer
On Jan 9, 2009, at 10:20 PM, Randall Smith wrote:
>
> After thinking about it some more, I think that the main issue is a
> lack
> of auto-commit in the DBAPI. Some drivers implement it and some
> don't.
> Though it's usually a good idea to use transactions, there are some
> times for real auto-commit.
theres a wide degree of variability in the presence and behavior of
transactions. if you use MySQL MyISAM, theres no transaction at all.
If you're on Oracle, there's always a transaction. Though I agree a
DBAPI level autocommit setting would have been nice.
> For example, I'm working on a thick client query tool in which a
> connection might be held open for days. There is an auto-commit
> mode as
> well as a transaction mode. In auto-commit mode, changes in the
> database from any session should be visible immediately.
You just need to ensure you clear any transactional state on the
connection before using it, or after you're done with it. the SQLA
connection pool accomplishes this nicely.
> The issue is really just when using SA as a high level DBAPI. Because
> transactions require explicit calls (e.g. trans = conn.begin()), I
> think
> that most people would imply that no transaction is in effect without
> this explicit call and would be surprised to discover there is one.
Its an interesting idea that the Connection object would be always
"transactional" in nature, we wouldn't have the Transaction interface
as it stands now and you'd just be saying Connection.commit()/
rollback() the same way as a DBAPI connection.
At the time I created it i wanted to restore the concept of a
"begin()" to the interface which DBAPI had removed. But really all
begin() says is "turn the autocommit mode off". So we did decide to
change the semantics of a DBAPI connection a bit here, but with
regards to the original design, i.e. there was no Connection, just
Engine which returned connections to the pool after each use, it
worked out fairly well. So I guess there's some history to this.
Explicit connection usage is not very common in any case since theres
usually not much need for it, unless you want to control the
transaction explicitly. Rethinking the interface of Connection at
this level is always an option but we'd need a seriously good reason
to do so since the transactional story of the library is one of the
most critical things an application is constructed around...I'd worry
that a change in that story would potentially throw away a lot of the
smooth sailing we've had for a couple of years.
>
> Maybe you didn't intend for it to be used like I'm using it, but I
> must
> say that for the most part, it does a great job of creating a standard
> interface for the various drivers. Also, I like ResultProxy,
> reflecttable and other goodies.
well I guess the "intent" part here is that you're keeping a
connection checked out of the pool for a long time. that is OK as
long as you do a rollback() on it after each period of usage. Seems
like it would be a lot easier to just use the pool though, "open" the
connection when you need it, "close" it when youre done. you also get
the benefit of pool_recycle if thats needed.
> Curious. Why would you choose not to use the driver's autocommit
> functionality if available? For example, with psycopg2, you could do:
>
> con.set_isolation_level(0)
if we started supporting that, it actually adds more code to SQLA
since our current autocommit mechanism has to become conditional, we'd
also need extra communication with the connection to "turn autocommit
off" when returned to the pool, etc. The current autocommit is
generic and behaves consistently across all backends, with almost no
backend-specific code. if we start using the autocommit
functionality of those drivers which provide it (i think sqlite has
one too), it would potentially introduce behavioral variability into
the equation, such as what kinds of statements COMMIT is issued for,
issues which may be specific to certain versions of the DBAPI, etc.
Again this is a question of what benefit we would get. If DBAPI did
provide autocommit() at the generic level, that would make it easier
since we could code to that feature exclusively.
Martijn Moeling
It is me again with an interesting thing, I've searched the net, this group etc. Not a lot of people seem interested in append_result, I AM!!
I am looking for a way to implement the following:
I have many tables, a lot with polymorphic inheritance and self and cross references.
In order to control "available" data I have set up a system similar to ACL (Access Control Lists)
Depending on "Who I am" I can get data from the database.
I want to do so within the MapperExtension I already have set up to do some "before update" and "before insert"
def append_result(self, mapper, selectcontext, row, instance, result, **flags):
if instance.__tablename__ == 'he':
return EXT_STOP
else:
return EXT_CONTINUE
would do such a thing, but I want (for the sake of the code behind that) to continue with a heavily modified instance.
To avoid making this long code (a lot of different object types pass through here, remember the polymorhic bit)
Does anyone have an interesting approach to this? basically I need to do something like instance= instance_class_type(new, configuration, based, on, the, ACL)
Any help would be wonderfull,
Martijn
Michael Bayer
On Feb 7, 2011, at 10:55 AM, Martijn Moeling wrote:
> Hi,
>
> It is me again with an interesting thing, I've searched the net, this group etc. Not a lot of people seem interested in append_result, I AM!!
>
> I am looking for a way to implement the following:
>
>
> I have many tables, a lot with polymorphic inheritance and self and cross references.
>
> In order to control "available" data I have set up a system similar to ACL (Access Control Lists)
>
> Depending on "Who I am" I can get data from the database.
>
> I want to do so within the MapperExtension I already have set up to do some "before update" and "before insert"
Limitations on inserts, updates and queries are best done outside of the Mapper. By the time the mapper is dealing with instructions to persist or load a row, its usually too late, unless you're looking to raise an exception upon certain conditions. For example there's no way to "stop" the insert from happening inside of a "before insert" operation, short of raising an exception (maybe that's what you're doing).
A SessionExtension.before_flush() OTOH allows you to modify everything that's going to happen before any flush plans are made.
Regarding append_result(), its a very old hook from 0.1 that's never had any real use. In this case I would instead be ensure that the undesired rows are not in the result set to start with. The recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery is a decent starting point for such a recipe.
>
>
> def append_result(self, mapper, selectcontext, row, instance, result, **flags):
> if instance.__tablename__ == 'he':
> return EXT_STOP
> else:
> return EXT_CONTINUE
>
> would do such a thing, but I want (for the sake of the code behind that) to continue with a heavily modified instance.
>
> To avoid making this long code (a lot of different object types pass through here, remember the polymorhic bit)
>
> Does anyone have an interesting approach to this? basically I need to do something like instance= instance_class_type(new, configuration, based, on, the, ACL)
>
>
>
> Any help would be wonderfull,
>
> Martijn
>
>
>
>
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Martijn Moeling
I will never ever stop a class from being saved/persistent,
it is the other way around. I thought I was able to use joins and or relations to limit for "allowed" results from a query.
With all the polymorphic and self references I have got and the fact that I need to do so for multiple Polymorphic colums I came up with the ACL idea.
Im not sure it if will perform, but in the create_instance I will look up the ACL and set additional properties on the instance or create an empty one.
This whole system is getting very complex now and limiting returned data involves modifying relationsships a lot. I'm really glad I got that working.
Many classes are base on top of that and I the only IT guy working on this together with "programmers" with a chemistry degree. Who will need the API I am working on to do their stuff without knowing anything about Databases........
To have that I Inherit polymorphicly, have many-to-many self references, use mixins.
I'll have a look at the PreFilteredQuery example you gave me, Any thoughts are helpful, I'll see If I can make up an example with persons and addresses again since the molecule stuff makes it even more confusing..
Will take me some time though
Martijn
Michael Bayer
On Feb 7, 2011, at 11:42 AM, Martijn Moeling wrote:
> I think, I might be helped with the create_instance "event"
Assuming you're talking about when the ORM establishes an instance from a newly fetched row, you can use the @reconstructor hook for that. 0.7 publishes this event additionally as the "load" event.
Martijn Moeling
I took a look at the recipe you indicated, it looks promising but the check should be constructed from database results. Another issue is that this project is implemented in my web based desktop/Os which uses SQLAlchemy from the bottem up. So modifiing the session object globally is with a PreFilteredQuery is not a real option. Creating a session for this "program" only might be an option but I am not sure how that will turn out.
Being it a web based (and so Handle request and die), Persistence is (to me) not very usefull and I need to "reload" everything for every action.
the @reconstructor hook seems too outdated. I moved to 0.6.6 last week, and only will upgrade to stable/production versions since in my case there is a lot to it.
I need to transparently add "being queried" functionality to mapped objects. This functionality is will be mixed in and should be able to limit the results when being queried. Since my class definitions are so complex I would like to make a (not functional) example on what I am in search of. and I will not bother you with chemistry stuff...
Class ACL(Base):
Id = Column(Integer, primary_key=True)
tablename = Column(Unicode(...
tableId = Column(Integer
RecordId = ForeignKeyContruct( / ForeignKey (not sure yet)
Record = relation( self.tablename
User_Group = relation to Person, group
Bool columns......
MayRead
MayWrite
MayCreate
Class Mixinstuff(Object)
Rights = {} # Rights["MayRead"] etc. will be set upon load....
Class Person(Base,Mixinstuff)
Id = Column(Integer, primary_key=True)
ACLs = relation('ACL' All ACL records which have tablename = 'person' and tableID = Person.Id, cascade="delete" and ACL record for me ) # ACL's work on many tables
I might not define the relation here but backref from the acl record depending on how to build what I want
addresses = relation( ....
Class Address(Base, ACLMixinstuff)
Id = Column(Integer, primary_key=True)
ACLs = relation('ACL' All ACL records which have tablename = 'person' and tableID = Person.Id, cascade="delete") # ACL's work on many tables
I might not define the relation here but backref from the acl record depending on how to build what I want
class ME()
userId = 1 (foreignkey to Person)
groups = [1,2,3,4] (relationship with groups (same polymorhic baseclass)
Now consider ME being a member of "Everyone" not "guest"
ACLS for Person
ME | table = person | Id = 1| MayRead = F
Everyone | table = person | Id = 1 | MayRead = T
Guest | table = person | Id = 1 | MayRead = F
user = ME, GROUPS = [Everyone]
A query for Session.query(Persons).all() should NOT return Person.Id although Everyone says True, personal Permissions overrule group permissions , simple boolean operations. If no ACLs are found It all defaults to false or true not sure yet on how this will work on my real data model, since this will be the model on which atoms and molecule connections are "Allowed"
If However the ACL's turn out that ME.MayRead = T, I will only get related addresses I actually may read. This should "work" automatically for each class with Mixedinstuff inherited....
This is whilst I do not want the "Users" of this model to be bothered with this, the should add data to their model and query to generate list of possible new molecules.
I am some sort of clueless on how to do this properly
the MapperExtention.append_result still seems the best way...
if calculate_ACLs(Session = object_session(self), tablename = instance.__tablename__, TableId = instance.__=TableId__, CheckFor = ME, Right = "MayRead" ):
EXT_CONTINUE
else:
EXT_STOP
Dont you?
One other thing, the CalculateACLs query should be as light as possible It will only need to return True or False if possible using database functions and if possible be database independant.
Can you help me on that one too?
def calculate-ACLs(.......):
BOOLGROUP = Session.query(ACL).filter(and_(tablename= .., tableId =...,USER_GROUP in me.literal_colum(..?..?..?))......
BOOLME = the same but now for ME, is easy no boolean calculation needed in query
if BoolME:
return BOOLME
else:
return BOOLGROUP
Martijn
Michael Bayer
On Feb 8, 2011, at 6:05 AM, Martijn Moeling wrote:
> Michael,
>
>
> I took a look at the recipe you indicated, it looks promising but the check should be constructed from database results. Another issue is that this project is implemented in my web based desktop/Os which uses SQLAlchemy from the bottem up. So modifiing the session object globally is with a PreFilteredQuery is not a real option. Creating a session for this "program" only might be an option but I am not sure how that will turn out.
Well a MapperExtension is also "global" to that class. Subclassing Query with rules for a specific mapper is fairly easy to isolate to those use cases.
>
> Being it a web based (and so Handle request and die), Persistence is (to me) not very usefull and I need to "reload" everything for every action.
That is typical for a web application.
>
> the @reconstructor hook seems too outdated. I moved to 0.6.6 last week, and only will upgrade to stable/production versions since in my case there is a lot to it.
@reconstructor is a standard feature since 0.5 and continues to be.
>
> I need to transparently add "being queried" functionality to mapped objects. This functionality is will be mixed in and should be able to limit the results when being queried. Since my class definitions are so complex I would like to make a (not functional) example on what I am in search of. and I will not bother you with chemistry stuff...
>
>
> user = ME, GROUPS = [Everyone]
>
> A query for Session.query(Persons).all() should NOT return Person.Id although Everyone says True, personal Permissions overrule group permissions , simple boolean operations. If no ACLs are found It all defaults to false or true not sure yet on how this will work on my real data model, since this will be the model on which atoms and molecule connections are "Allowed"
>
> If However the ACL's turn out that ME.MayRead = T, I will only get related addresses I actually may read. This should "work" automatically for each class with Mixedinstuff inherited....
>
> This is whilst I do not want the "Users" of this model to be bothered with this, the should add data to their model and query to generate list of possible new molecules.
>
> I am some sort of clueless on how to do this properly
>
> the MapperExtention.append_result still seems the best way...
>
> if calculate_ACLs(Session = object_session(self), tablename = instance.__tablename__, TableId = instance.__=TableId__, CheckFor = ME, Right = "MayRead" ):
> EXT_CONTINUE
> else:
> EXT_STOP
>
> Dont you?
I guess what you're expressing is that your ACL rules need to fire off using Python code, not SQL expressions. The whole thing seems quite awkward to me since there's nothing to stop someone from saying Query(MyACLObject.id, MyACLObject.name, ...), etc., they get all the data from the ACL row anyway, or similarly if they were to say Query(SomeClass, SomeOtherClass, MyACLObject) using a join, again the append_result() hook isn't used. If it were me I'd be using some filter function around query() in an explicit sense to do it, but this is just a matter of style. The hook will work fine if its limitations are OK with you.
Martijn Moeling
Thank you,
The final solution has nothing to do with ACL's or addresses and security for others getting results by querying is a none issue.
As mentioned before I am building a database and tools to help chemists selecting molecule structures. It is all way more complex than you might think since the "ACL" records have "ACL" records assosiated to them to.
Setting up relations and queries is a total nightmare because almost all relations end up to be circular over multiple tables. controlling the eager loading where possible for convenience and where impossible has been a huge job although SQLAlchemy is a huge help.
I only use this as a understandable data structure since I know how hard it was to understand the terminology. I do not want to bring that to this group and more importantly since I search the mailinglist myself a lot it can help others finding a solution to their needs. I find that the deeper I dive into SA, the less examples are available, the harder it is to test functionality and sometimes documentation gets more sparse.
Thank you again...
Martijn