On Thursday 28 June 2007, Michael Bayer wrote: > On Jun 28, 1:40 am, "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote: > > On Wednesday 27 June 2007, Michael Bayer wrote: > > > this issue can be worked around by using explicit transactions. > > > > actually no, it can't. Maybe I don't get it right, but the only way for > > me to get a commit was actually to modify Connection._autocommit in > > sqlalchemy.engine.base. > > either TG is getting in the way, or youre not getting it right. if > anyone ever needs to modify the internals of SA to get something to > work, i would *highly* prefer if they could email the ML or post a > trac ticket with their issue so that it may be handled properly.
I agree. I didn't put this on the ML or trac because I just don't know why it behaves like that and frankly I just gave up on it after a couple of hours and took the shortcut. Obviously TG wraps every request into a transaction and commits/rollback's that when the controller method returns the response. There is a valid transaction object in the tg.sa_transaction variable which should allow to end the outer transaction and start a new one on your own. Although I can do that, the result was always the same - SA doesn't see the transaction and runs into the base._autocommit method (which I think it shouldn't if the transaction was handled properly before that). Tracking what the DB gets, it never sees a rollback/commit when I tell the original transaction object in tg.sa_transaction to rollback. The command is just ignored. When it comes to submitting a patch: I do have code changes for SA to handle arrays and user defined types for postgresql in the autodetect code. So I might roll that up and send it over when I have a minute. > > > Obviously SA thinks there is no transaction in TG, > > so it just wraps one around it. > > if TG actually has "a transaction" going on, theyd certainly have to > configure SA to be aware of it (most likely via SessionTransaction). > if not, then yes things arent going to work at all (though still, an > explicit SA transaction should work all by itself). The tg.sa_transaction variable contains a SessionTransaction object at that time. So they obviously do something to get that. > > I agree, and that is certainly DB dependent. Personally I can't imagine > > that an automatically issued rollback for every select transaction is in > > any way more overhead than issuing a commit. Not wrapping a select in a > > transaction will definitely be the least overhead. > > we dont issue a rollback for every select transaction. we issue a > rollback when a connection is returned to the pool. you can check out > a connection explicitly and perform any number of selects on it > without any rollbacks or commits. > > because the "rollback" is at the connection-pool checkin level, it > should be more apparent how inappropriate it would be to issue a > *commit* every time a connection is returned to the pool, an operation > that knows nothing about what just happened with that connection. the > rollback is to release database locks. > > im thinking that it might be time to allow an option in SA that just > turns the DBAPI's "autocommit" flag on. that way you can just blame > the DBAPI for whatever issues arise. :-) Yes. I bet the result would be the same in my case. > > its not always possible to "not wrap a select in a transaction". > oracle for example *always* has a transaction going on, so everything > is in a transaction in all cases. > > > > that a stored-procedure-oriented application is "far more efficient" > > > is *extremely* debatable and database-dependent as well. > > > > I doubt it's *extremely* debatable. > > its extremely debatable: > > http://www.google.com/search?q=stored+procedures+vs > > > Just issue 100 inserts from inside a > > stored procedure (or 100 updates) and do the same discretely with any > > kind of db interface. In case of the interface every statement has to be > > parsed by the db, whereas in a stored procedure the statement is already > > "compiled" of sorts (at least Oracle and PostgreSQL do that). > > the debate over SP's is about a larger issue than "is an SP faster > than 5 separate INSERT statements". SP's are of course much better > for micro-benchmarks like that. its their impact on application > development and architecture where the debate comes in (read some of > the googled articles). > > I am certainly not anti-SP, ive done pure SP applications before (on > projects where the DBAs controlled the SPs)...I just dont want to > start hardwiring SQLAlchemy to expect that sort of application. I > think 80/20 as applied to SELECT is that 80% of SELECTs are for read > operations and a COMMIT is inappropriate. I agree with your goals and I also agree that a lot of people don't use stored procedures and similar db-dependent things. All I'd need is a way to tell SA that this one select is something worth committing. > > > if you > > > really want COMMIT for every SELECT, i'd favor it being enabled via an > > > option passed to create_engine(). > > > > Not every select, every transaction that didn't roll back. > > I just think the default of rollback on every transaction is wrong - a > > rollback should occur when there is a problem, not when the transaction > > was fine. But that may just be me. > > this is the use case: > > c1 = pool.connect() > row = c1.cursor().execute("select * from sometable").fetchone() > pool.return_connection(c1) > > c2 = pool.connect() # (returns a connection that is not c1) > c2.cursor().execute("drop sometable") # --> deadlock > > if DBAPI supported a "release_locks()" method, we'd be using that. > > > Probably because a lot of people can't figure out how to use stored > > procedures and triggers, since the "lightweight/open-source" programming > > is often done on a database that has very limited support for both :-) > > keep in mind youre including the vast Hibernate community, including > its creators, etc. im not sure if the "im to dum to use stored > procedures" argument can fully explain why the SP-architecture remains > a minority use case. i think the overall inconvenience of it, the > clunky old languages you have to use on DBs like Oracle and SQL > Server, as well as the harsh resistance it puts up to so-called agile > development methods are better reasons. > > > Personally I'm not a big fan of handling database integrity outside the > > database. > > ....continue SP arguments..... > > thats great, you can have your preferences..the google link above > should reveal that quite a few people have established their > preferences in this matter. If you are truly writing an SP-only > application which prevents direct SQL access (that was the kind of SP > app I worked on), a tool like SQLAlchemy is mostly superfluous. It's not SP only. I have maybe 20 SP's. The majority is views and very little access goes to the tables directly (only for changes). The majority of data comes from 2 different client applications, neither of which uses SA. The third is a web interface which is mainly readonly, so SA simplifies a lot of things on the web-end. > for my purposes as the maintainer of SQLAlchemy, i need to support the > majority of use cases which is that of a non-SP oriented application. > not just because SQL-oriented apps are more common, but also because > SP-oriented apps aren't going to have use for higher level SQL > toolsets anyway since all the SQL is behind stored procedures. Again, not SP only. I only use SP's for pieces where I just don't want to recode business logic for the different client applications. Coding this stuff in each client is a nightmare to maintain (particularly because one of them is an old legacy system - AS400 ring a bell? Yeah, the financial industry is still a long way behind....) > > the end). In the end I think it's a SA issue - there should be a > > parameter allowing to tell SA that this "select" is actually something > > transactional and needs to commit if no error is raised. > > this is this: > > michael bayer wrote: > > i > > also have a notion of SQL functions being marked as "transactional" to > > help this issue. > > but really, i think you should get your explicit transactions > working...i would imagine you have the need to execute multiple SPs in > one transaction (otherwise that must be some enormous SP youre > running). If I could figure out how, I probably would. The code from the docs doesn't work, because TG does something around that. I guess one of the major drawbacks of TG is it's documentation (or lack thereof) when it comes to using SA together with it. I guess that will change once TG uses SA by default. Uwe -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax: +1 707 568 6416 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~----------~----~----~----~------~----~------~--~---
