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 > > <https://groups.google.com/d/msgid/sqlalchemy/4a1d8ea8-fbeb-4158-a791-3c4780602c5a%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/e32c4cd1-f866-4e65-9c5a-34d795c84a92%40www.fastmail.com.
