postgresql - 在 sqlalchemy 中手动设置 id 时的竞争条件
问题描述
我希望我的所有对象都有一个由 PostgreSQL 设置的唯一 id,带有一个(序列)和另一个取决于第一个的 id。创建对象时,如果我在保存后设置第二个 id,我将在表上有一个 INSERT 和一个 UPDATE,这并不是最好的。所以只有一个 INSERT 我从 PostgreSQL 序列中获取 id 并用它设置 id 而不是让 PostgreSQL 在 INSERT 阶段执行它。我对 SQLAlchemy 很陌生,并且想确保这种做法是竞争条件证明。
感谢您对这个想法的想法
class MyModel:
def __init__(self, session, **data):
"""
Base constructor for almost all model classes, performing common tasks
"""
cls = type(self)
if session:
"""To avoid having an UPDATE right after the INSERT we manually fetch
the next available id using a postgresl internal
SELECT nextval(pg_get_serial_sequence('events', 'id'));
To do that we need the table's name and the sequence
column's name, by chance we use the same name in all our
model
"""
table_name = cls.__tablename__
qry = f"SELECT nextval(pg_get_serial_sequence('{table_name}', 'id'))"
rs = session.execute(qry)
# TODO : find a non ugly way to to that
for row in rs:
next_id = row[0]
# manually set the object id
self.id = next_id
# set the external_id before saving the object in the database
self.ex_id = cls.ex_id_prefix + self.id
session.add(self)
session.flush([self])
解决方案
如果您的目标是 Postgresql 12 或更高版本,您可以使用生成的列。SQLAlchemy 的Computed 列类型会创建这样一个列,我们可以传递一个 SQL 表达式来计算值。
该模型将如下所示:
class MyModel(Base):
__tablename__ = 't68225046'
ex_id_prefix = 'prefix_'
id = sa.Column(sa.Integer, primary_key=True)
ex_id = sa.Column(sa.String,
sa.Computed(sa.text(":p || id::varchar").bindparams(p=ex_id_prefix)))
产生这个 DDL
CREATE TABLE t68225046 (
id SERIAL NOT NULL,
ex_id VARCHAR GENERATED ALWAYS AS ('prefix_' || id::varchar) STORED,
PRIMARY KEY (id)
)
和一个插入语句
2021-09-19 ... INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-09-19 ... INFO sqlalchemy.engine.Engine INSERT INTO t68225046 DEFAULT VALUES RETURNING t68225046.id
2021-09-19 ... INFO sqlalchemy.engine.Engine [generated in 0.00014s] {}
2021-09-19 ... INFO sqlalchemy.engine.Engine COMMIT
对于 Postgresql 的早期版本,或者如果您不需要将值存储在数据库中,您可以使用混合属性来模拟它。
import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import cast
Base = orm.declarative_base()
class MyModel(Base):
__tablename__ = 't68225046'
ex_id_prefix = 'prefix_'
id = sa.Column(sa.Integer, primary_key=True)
@hybrid_property
def ex_id(self):
return self.ex_id_prefix + str(self.id)
@ex_id.expression
def ex_id(cls):
# See https://stackoverflow.com/a/54487891/5320906
return cls.ex_id_prefix + cast(cls.id, sa.String)