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
-~----------~----~----~----~------~----~------~--~---
