Thanks for the comprehensive answer, Mike, this is helpful. Just to confirm, the per-instance version of your solution eagerly queries the value when assigning to foo.pk, not when foo is added/committed to the DB, right? And so this creates a race condition where multiple instances could be assigned the same computed PK?
But am I correct that with the per-model, ColumnDefault version, the query is performed at insert-time, meaning that race condition doesn't exist? Thanks for the clarification! -Gabriel On Friday, 18 July 2014 12:22:47 UTC-4, Michael Bayer wrote: > > Hopefully I’ll be able to add this in 1.0 using the patch illustrated at > https://bitbucket.org/zzzeek/sqlalchemy/issue/3133/support-dbs-that-magically-get-sql > . > > > On Jul 18, 2014, at 11:54 AM, Michael Bayer <mik...@zzzcomputing.com > <javascript:>> wrote: > > > On Jul 18, 2014, at 4:33 AM, Johannes Klug <klug.j...@gmail.com > <javascript:>> wrote: > > > results in the stacktrace: > > InvalidRequestError: Instance <Foo at 0x224f710> cannot be refreshed - it's > not persistent and does not contain a full primary key. > > > so I hate to be a stickler here but that’s not a stack trace, that’s an > error message. > > if we reproduce your full script we can see what is going on. > > First, always use echo=True to see what’s happening. We can see that the > statement does in fact work as planned: > > INSERT INTO foo (pk, bar) VALUES ((SELECT coalesce(max(foo.pk) + ?, ?) AS > coalesce_1 > > however, the error then happens later on, with this partial trace: > > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", > line 389, in finalize_flush_changes > self.session._register_newly_persistent(other) > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line > 1408, in _register_newly_persistent > instance_key = mapper._identity_key_from_state(state) > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line > 2285, in _identity_key_from_state > for col in self.primary_key > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", > line 580, in get > value = callable_(state, passive) > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py", line > 423, in __call__ > self.manager.deferred_scalar_loader(self, toload) > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/loading.py", line > 597, in load_scalar_attributes > "contain a full primary key." % state_str(state)) > sqlalchemy.exc.InvalidRequestError: Instance <Foo at 0x10165d510> cannot > be refreshed - it's not persistent and does not contain a full primary key. > > we can see the SQLAlchemy ORM, like all ORMs, needs to know the primary > key of this newly inserted object - “identity key from state”. SQLAlchemy > is failing pretty ungracefully here. This could be improved. > > To understand the full picture here is pretty complicated because there > are a lot of nooks and crannies to the reality here so I will try to bullet > them out: > > 1. Typically, it’s not possible to avoid a failure here. SQLite does not > support RETURNING, so the only way we can get at the value when it is done > inline like this is through cursor.lastrowid. Cursor.lastrowid typically > only gives us the value that was generated using the database’s sequencing > mechanism. On MySQL, we would have to use an AUTOINCREMENT column; we get > a zero back from lastrowid instead. > > 2. Apparently, based on a short test, recent pysqlite is giving us the > actual value here in cursor.lastrowid. I’ve never seen a DBAPI do this > before and SQLAlchemy would need additional enhancements to distinguish > between databases where lastrowid only works for auto increment values, and > lastrowid that actually gives you the value no matter where it came from. > That’s not available right now. > > 3. If we run this script as is on a database that supports RETURNING, the > whole thing works just fine. Here’s Postgresql: > > INSERT INTO foo (pk, bar) VALUES ((SELECT coalesce(max(foo.pk) + > %(max_1)s, %(param_1)s) AS coalesce_1 > FROM foo), %(bar)s) RETURNING foo.pk > > > 4. So for now, for ORM use (which requires the PK, as opposed to Core > where this isn’t needed since there is no object to track), to use this > pattern on a lastrowid backend the statement needs to be invoked outside of > the INSERT. We can achieve this using ColumnDefault: > > class Foo(Base): > __tablename__ = 'foo' > pk = Column(Integer, primary_key=True) > bar = Column(Integer) > > Foo.__table__.c.pk.default = > ColumnDefault(sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, > 1)]).as_scalar()) > > Where we’ll see: > > SELECT (SELECT coalesce(max(foo.pk) + ?, ?) AS coalesce_1 > FROM foo) AS anon_1 > (1, 1) > INSERT INTO foo (pk, bar) VALUES (?, ?) > (1, None) > > > This will work on any backend. > > Or if the “per instance” system is needed, just run the statement: > > foo = Foo() > foo.pk = > session.scalar(sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)])) > session.add(foo) > session.commit() > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
