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