Thank you for the help! The approach with the context-sensitive default function worked out well for me. I especially like how it keeps the "magic" close by to the rest of the code, hopefully reducing surprises when I have to come back to this code eventually.
I have an idea for a "namespaced sequence factory" which I think might be cool. I’ll post it to the group here if it works out. Thanks again, Scott Colby On Oct 4, 2019, 19:12 -0400, Mike Bayer <mike...@zzzcomputing.com>, wrote: > > > On Fri, Oct 4, 2019, at 3:57 PM, Scott Colby wrote: > > Hello, > > > > I am attempting to implement a "gapless" sequence using SQLAlchemy's > > event listeners instead of DB-level triggers inspired by > > [this](https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences) > > and similar approaches. (In fact, I don't particularly care about > > the gapless property but more about a "namespaced" sequence: my > > application has many parents, each with many children. I want to > > give the children numbers grouped by their parent.) > > > > I have created a minimal example which I have uploaded as a gist > > [here](https://gist.github.com/scolby33/e09624839a2b422312113d22b4e56da5). > > It can be run as `python namespaced_sequences.py` or provided with > > an optional argument specifying a DB URL, e.g., > > `python namespaced_sequences.py "postgresql://..."`. > > > > The example contains the declarative classes, the necessary boilerplate > > for setting up SQLAlchemy, my event listeners, and a main function > > that exercises the code with asserts specifying my desired behavior. > > > > The event listener `insert_child_number_sequence` seems to work > > correctly: upon the insertion of a row in the `parent` table, a > > matching row is inserted in the `child_number_sequence` table. > > > > My problem arises with the `set_child_number` listener. As can be > > seen when running the example, the ORM properly updates the > > `child_number_sequence` row with SQL like > > > > UPDATE child_number_sequence > > SET last_child_number=(child_number_sequence.last_child_number + 1) > > WHERE child_number_sequence.parent_id = 1; > > > > This is what I would expect for an atomic increment in SQL. > > > > But the insert of the new child issues SQL like > > > > INSERT INTO child (parent_id, child_number) > > VALUES (1, child_number_sequence.last_child_number + 1)); > > > > which fails with `no such column: child_number_sequence.last_child_number` > > (on SQLite; `missing FROM-clause entry for table "child_number_sequence"` > > on PostgreSQL). > > > > I think I can see what is going on here--my assignment in Python > > is propagating directly to the parameters used in the `INSERT` > > statement. In PostgreSQL, I would instead use a `RETURNING` clause > > to get the new value and in SQLite, I would use `BEGIN IMMEDIATE` > > to ensure that I have a write lock on the DB before reading anything. > > > > I attempted several approaches to have SQLAlchemy use the new value > > as computed by the database. For instance, adding > > `session.add(child_number_sequence); session.refresh(child_number_sequence)` > > between the increment and the assignment of the value to > > `instance.child_number`. Interestingly, this issues two `SELECT`s > > but completely swallows the `UPDATE`; the child gets assigned child > > number 0. > > > > I considered using the statements API to use the PostgreSQL `RETURNING` > > clause or `SELECT FOR UPDATE` but I would prefer to only have one > > code path for PostgresSQL and SQLite (and others?) if possible. > > > > well you definitely have to have slightly different code for RETURNING vs. > non. I would also lose the ORM stuff for this sequence (e.g. the > ChildNumberSequenceClass) as it's making things complicated and instead do > what you need to do with the before_insert handler, where you have access to > the Connection being used to emit SQL. Run the UPDATE (or INSERT) that you > need on the sequence table using Core > connection.execute(table.update().values(...)), and use RETURNING if the > dialect supports it (use connection.dialect.implicit_returning), otherwise > retrieve it using a SELECT. Then populate that value on the object that is > passed to before_insert(). > > Alternatively, you could do this whole thing using a Python side default as > well. the approach at > https://docs.sqlalchemy.org/en/13/core/defaults.html#context-sensitive-default-functions > illustrates a "context", which has a cursor right there, however in this > case you'd be using raw SQL so is not as abstracted as you might prefer. > > > > > > > > > > > > > > > > Unfortunately, I'm stumped as how to do this. What is the right > > approach to implement this pattern? > > > > Thank you, > > Scott Colby > > > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > 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 view this discussion on the web visit > > https://groups.google.com/d/msgid/sqlalchemy/4a1d8ea8-fbeb-4158-a791-3c4780602c5a%40googlegroups.com. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to a topic in the Google > Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/pIKZRRjjM7A/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e32c4cd1-f866-4e65-9c5a-34d795c84a92%40www.fastmail.com. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/82d6a4be-e15c-4f3d-b82f-3d5ffb5358b3%40Spark.
