Thanks Mike for the response. Comments below.
On Sep 27, 6:47 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Sep 27, 2009, at 4:11 AM, nkhalasi wrote:
>
> in theory, you want to say session.flush() so that "n.next_id" is
> persisted but the transaction is not committed.
>
> However the approach you have above wont work in any case assuming
> the INSERT itself takes place within Session.flush() - the reentrant
> call to flush should be raising an error immediately. maybe you're
> on an older SQLA version that doesn't raise this assertion which is
> why it chokes later on the double commits (which is again, an
> assertion that something isnt being called in the proper sequence).
>
> you don't want to be using the ORM layer within a Column default
> generator. Just the inefficiency alone of retrieiving an entire
> object and doing a whole flush for every row in an enclosing series
> of inserts makes it a bad idea. do this instead:
>
> def get_next_id(context):
> r = context.connection.execute(select([pk_generator_table]).where
> (pk_genrator_table.c.table_name=='principals'))
> row = r.fetchone()
> if row:
> id = row['next_id']
> if id > row['end_id']:
> raise Exception(...)
> context.connection.execute(pk_generator_table.update().where
> (pk_genrator_table.c.table_name=='principals').values
> (next_id=pk_generator_table.c.next_id + 1))
> return id
> else:
> raise Exception(...)
>
> the above will get you out the door but still does a lot of work, if
> you had a large volume INSERTs. Ideally you'd use a MySQL stored
> procedure or trigger to generate the new values, SQLAlchemy would
> just fetch the new values after the fact.
>
I had tried this (which is similar to what you described above)
def newid(ctx):
log.debug('generator.newid() invoked with context = %s' %ctx)
id = ctx.connection.execute(select([pkgt.c.next_id],
pkgt.c.table_name=='principals',
for_update=True)).scalar()
log.debug('Complied? %s, Autocommit? %s' %(ctx.compiled,
ctx.should_autocommit))
ctx.connection.execute(pkgt.update(values={pkgt.c.next_id :
pkgt.c.next_id +1}))
log.debug('Finished writing back the next id')
return id
However with this I am getting unwanted commits. Essentially when the
newid(ctx) function executes the update it also does an commit which
results into my data committed which I would have otherwise expected
to be committed at some other point. I am trying to figure out how can
this update of next ID be done along with my regular application logic
commit.
Also what I am doing here is just a dummy hook. So my final version
will be lot more different.
Regards,
Naresh
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
