Closed
Description
Discussed in #10259
Originally posted by PHvL August 18, 2023
We are using the Versioned class from history_meta for tables that are synced from some external source. Sometimes rows disappear in the external source and later re-appear with the same primary key,
The result is that both the history table and the original table contain a row with version=1, resulting in a violation of the primary key constraint of the history table at the next update (or deletion) of this row.
I've hence changed the default value of the version column in the history table to get the maximum of the version already present in history columns +1
I don't know if this is the most elegant solution, but maybe it helps someone with the same issue.
@@ -11,6 +11,9 @@ from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import PrimaryKeyConstraint
from sqlalchemy import util
+from sqlalchemy import select
+from sqlalchemy.sql.expression import func, and_
+from sqlalchemy.engine.default import DefaultExecutionContext
from sqlalchemy.orm import attributes
from sqlalchemy.orm import object_mapper
from sqlalchemy.orm.exc import UnmappedColumnError
@@ -146,8 +149,15 @@ def _history_mapper(local_mapper):
super_history_table.append_column(col)
if not super_mapper:
+ def default_version_from_history(context: DefaultExecutionContext):
+ current_parameters = context.get_current_parameters()
+ return context.connection.scalar(select(func.coalesce(func.max(history_table.c.version), 0)+1).where(and_(*[getattr(history_table.c, c.name)==current_parameters.get(c.name, None) for c in inspect(local_mapper.local_table).primary_key])))
+ # Set default value of version column to the maximum of the version in history columns already present +1
+ # Otherwise re-appearance of deleted rows would cause an error with the next update
local_mapper.local_table.append_column(
- Column("version", Integer, default=1, nullable=False),
+ Column("version", Integer,
+ default=default_version_from_history,
+ nullable=False),
replace_existing=True,
)
local_mapper.add_property(
```</div>Metadata
Metadata
Assignees
Labels
Type
Projects
Milestone
Relationships
Development
No branches or pull requests
Participants
Activity
zzzeek commentedon Aug 21, 2023
test case submitted by reporter
sqla-tester commentedon Aug 1, 2024
Mike Bayer has proposed a fix for this issue in the main branch:
add check for pre-existing history records https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/5435
sqla-tester commentedon Aug 1, 2024
Mike Bayer has proposed a fix for this issue in the rel_2_0 branch:
add check for pre-existing history records https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/5436
add check for pre-existing history records
add check for pre-existing history records