Python API for Postgresql Sequences

Feature Requests
1

Could we have a Python API for creating and using Postgresql sequences?

Something like:

anvil.tables.create_sequence("my_sequence", 101)
print(anvil.tables.sequence.my_sequence.next())
print(anvil.tables.sequence.my_sequence.next())
anvil.tables.sequence.my_sequence.drop()

>>> 101
>>> 102

to wrap:

CREATE SEQUENCE my_sequence START 101;
SELECT nextval('my_sequence');
SELECT nextval('my_sequence');
DROP SEQUENCE my_sequence;

Use Case

At present, I use a ‘sequences’ table with columns for the name and the next value. If I need a value, often for use in another table, I get it, use it and update it within a transaction.

However, in apps with particularly heavy loads, I often get transaction conflicts using this method. I’m almost certain those are spurious conflicts, but I’d really rather not relax the transaction strictness when postgresql has a much better mechanism built in.

3 Likes
2

Do you really need a sequence, or just a unique value to use as a key? In the latter case you could switch to a uuid version 4. You can generate them in Python code without going to the database. The function to generate one is uuid4() and lives in the uuid package in the standard library.

3

Yes, I need a sequence. I’m fully aware of the uuid package (and its partner client side library in anvil extras).

1 Like