On Wednesday 27 June 2007, Michael Bayer wrote: > On Jun 27, 8:24 pm, "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote: > > And on that note: if you're using SA with TG, SA issues a rollback on > > every transaction that is not an insert or update. So if you're having a > > stored procedure (which you trigger with "select * from stored_proc()" > > and that stored procedure actually does updates or inserts, you're going > > to lose changes - simply because SA issues a rollback on Select > > statements. > > 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. Obviously SA thinks there is no transaction in TG, so it just wraps one around it. After I got tired of searching for the problem I just added SELECT to the above method and now get my commit. I'm sure either I do something wrong or there's a bug in the db implementation of TG. All I could find is that the transaction (since 1.0.2 available in tg.sa_transaction) is just a subtransaction from the autocommit code - thus when the outer transaction issues a rollback the inner transaction that was committed will be rolled back too. > i also have a notion of SQL functions being marked as "transactional" to > help this issue. but this thread so far seems to be about the notion > of an entire request being marked as "transactional", which as it > turns out is a central concept of J2EE and others, which would also > eliminate the issue youre having. Yes - see below on the "every select statement". > > > Quick fix for this is to modify SA to just issue a commit on every > > statement, so the TG transaction can roll back or commit without being > > affected. IMHO issuing a commit on a select shouldn't be more overhead > > than issuing a rollback - because the db should know what to do (in this > > case nothing) > > this would be something that would need to be benchmarked. i do think > it would add some overhead. but beyond that, i dont like the idea of > unnecessary COMMITs for every SELECT statement at all. 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. > > > Maybe something to think about too, because I can't be the only one > > making heavy use of stored procedures (which are far more efficient than > > controller/model side code) > > that a stored-procedure-oriented application is "far more efficient" > is *extremely* debatable and database-dependent as well. I doubt it's *extremely* debatable. 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). I had cases where moving the application code (standard DBAPI calls, no ORM) to a stored procedure reduced the execution time from 27 seconds to 2 seconds without changing the database structure (ok, extreme case, but handling 1500 inserts discretely in the application is just a lot of remote overhead, particularly when the requests come over the network where you get an extra delay for network operations) > 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. > Beyond that I think the "model > implemented as stored procedure" style of development is much in the > minority these days, particularly within the "lightweight/open-source" > development community. just that the stored procedure changes the > semantics of SELECT to be a "write" operation reminds me of the > RESTful sin of using GET to post data. 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 :-) Personally I'm not a big fan of handling database integrity outside the database. Take a simple portal as example, where every new user who signs up gets a couple of mailboxes by default (inbox, outbox,drafts,trash). I could handle that in the db structure itself, but that would make the db model overly complicated for such a simple thing. The proper way is to handle that in a trigger, or I can handle it in a stored procedure that just makes sure the mailboxes are created. In my case I chose a stored procedure because it's actually a select on the user's profile, but the stored procedure also does certain sanity checks and inserts/updates stuff according to its findings. Handling the same in the application code is IMHO the least desirable solution, simply because I can test the trigger/stored procedure once and I know everything works until I change the database structure. Application code is meddled around with much more, so the chance of introducing a bug is higher on that end. You could argue that using SA with stored procedures defeats the "portability" approach. I agree that you can't easily move a Oracle or PostgreSQL or DB2 based application with stored procedures to a different database, but how many people do that? If you build a generic application the argument is valid, but if you build something for a specific purpose, the database will never be changed for the life of the application (because generally "special purpose" applications are funded by someone and those people usually don't invest another couple grand just to make it work with some other database). I agree on the semantics, but I don't see a lot the folks at postgresql can do about it: a) it's the SQL standard, b) one would have to have two different ways of executing a stored procedure (which there are, EXECUTE and SELECT) depending on whether the procedure has a return value or not and whether the procedure changes data or not (in my case it has a return value and changes data, so the SELECT is actually the correct way, but still I need a commit at 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. 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 -~----------~----~----~----~------~----~------~--~---
