I noticed this thread on django-devs:
http://groups.google.com/group/django-developers/browse_frm/thread/521a03a726d526e1/b1bacc5628341129?lnk=gst&q=psycopg2&rnum=1#b1bacc5628341129
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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
