Yeah, I think I explained badly.
What I was trying to show with the sqlalchemy vs postgres query logs
is that extra BEGIN that psycopg2 is sending before the SELECT that
sqlalchemy executes. The BEGIN is implicitly added by psycopg2 because
it's in its default transaction isolation state of read comitted.
Which I didn't know was the DBAPI default -- so strike what I said
about changing any default behavior.
But It would be nice to be able to have total control over the
transactional state of the connection, so that when I know that I'm
just doing a select or two I don't have to have the overhead of a
BEGIN that I know is useless, but as things stand I can't do that,
because do_begin isn't implemented for any of the dialects. I guess
that's because sqlalchemy is depending on the underlying DBAPI
implementation to handle sending BEGIN statements before the first
query that is outside of a transaction?
I noticed a TODO about implementing true autocommit behavior, which is
what I need -- a way to tell the dbapi module *not* to start those
implicit transactions. If the engine/connection could be put into true
autocommit mode, and issue connection.begin() from do_begin when in
that mode, then I could do everything I want to do and I don't think
anything would break, since the orm calls do_begin before each flush.
I know how to change the isolation level in the sqlite and postgres
dialects -- would you be interested in a patch or work on a branch
where I added an isolation_level property to the engine, implemented
it for those two, and for those two dialects had do_begin issue a
connection.begin() when the isolation_level was set to autocommit? I
could probably tackle mysql as well, but I have no access to mssql or
oracle, so I can't do those.
Hopefully I'm making some more sense this time...
JP
On Feb 12, 12:42 am, "Michael Bayer" <[EMAIL PROTECTED]> wrote:
> im confused. the django thread seems to be saying that it is setting
> psycopg2 *into* autocommit mode, which causes the problem; the default
> setting of autocommit for DBAPI (and maintained in SA) is False. When
> i wrote frameworks years ago I always thought "autocommit" mode was
> the way to go but i since learned that issues like this arise so SA
> *never* uses autocommit mode on the connection; instead it implements
> its own "autocommitting" behavior in a manner similar to hibernate -
> it looks at the statement being executed, determines if its a CRUD/
> CREATE/DROP expression, and then explicitly calls COMMIT if no SA
> transaction is already in progress.
>
> the log you have above doesnt prove that anything unusual is going on,
> since you are illustrating a transactional operation, then a SELECT,
> then another transactional operation, then another SELECT. SA does an
> explicit COMMIT for the CREATE statements since they are required to
> complete the table creation operation.
>
> this test confirms that psycopg2 defaults to "autocommit" as false and
> doesnt do anything with transaction modes after the connection is
> opened:
>
> import psycopg2 as psycopg
>
> conn = psycopg.connect(user='scott', password='tiger',
> host='127.0.0.1', database='test')
>
> for x in range(0, 5):
> curs = conn.cursor()
> curs.execute("SELECT 1")
> curs.close()
>
> log output:
>
> LOG: statement: SET DATESTYLE TO 'ISO'
> LOG: statement: SHOW client_encoding
> LOG: statement: SHOW default_transaction_isolation
> LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> LOG: statement: SELECT 1
> LOG: statement: SELECT 1
> LOG: statement: SELECT 1
> LOG: statement: SELECT 1
> LOG: statement: SELECT 1
> LOG: statement: ABORT
>
> one connection, five new cursors, only one set of "setup"
> corresponding to the connection.
>
> On Feb 9, 3:52 pm, "JP" <[EMAIL PROTECTED]> wrote:
>
> > I noticed this thread on django-devs:
>
> >http://groups.google.com/group/django-developers/browse_frm/thread/52...
>
> > Which notes that psycopg2 by default starts transactions behind the
> > scenes, even for select statements. If you happen to be running a web
> > app where each hit starts a new cursor, and you only do a few selects,
> > this adds a *lot* of overhead relative to autocommit.
>
> > I wondered if sqlalchemy suffered from the same problem with psycopg2,
> > and it does. Take a look at what sqlalchemy thinks it was doing for a
> > short example, vs what the postgres query log contains.
>
> > The code:
>
> > >>> import sqlalchemy as sa
> > >>> meta = sa.DynamicMetaData()
> > >>> users = sa.Table('users', meta, sa.Column('id', sa.Integer,
> > >>> primary_key=True, autoincrement=True), sa.Column('name', sa.String(50)))
> > >>> meta.connect('postgres://<snip>', debug=True)
> > >>> meta.create_all()
> > >>> users.select().execute()
>
> > sqlalchemy query log:
> > select relname from pg_class where lower(relname) = %(name)s
> > CREATE TABLE users (
> > id SERIAL NOT NULL,
> > name VARCHAR(50),
> > PRIMARY KEY (id)
> > )
> > COMMIT
> > SELECT users.id, users.name FROM users
>
> > postgres query log:
> > LOG: statement: SET DATESTYLE TO 'ISO'
> > LOG: statement: SHOW client_encoding
> > LOG: statement: SHOW default_transaction_isolation
> > LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> > LOG: statement: select relname from pg_class where lower(relname) =
> > 'users'
> > LOG: statement:
> > CREATE TABLE users (
> > id SERIAL NOT NULL,
> > name VARCHAR(50),
> > PRIMARY KEY (id)
> > )
> > LOG: statement: END
> > LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> > LOG: statement: SELECT users.id, users.name FROM users
>
> > I think it would be better for sqlalchemy to set psycopg2 to
> > autocommit by default, and implement do_begin in the dialect so that
> > transactions are only started when desired, rather than implicitly on
> > the first statment seen by a cursor when there's no current
> > transaction, as seems to be the case now.
>
> > Mike, would you be interested in a patch that implemented that
> > behavior? Does anyone disagree that that would be better?
>
> > JP
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
