How to switch to native versioning with existing version tables?

[evrys]

Hi there, I have a big project which has been using sqlalchemy-continuum for a long time. I'm trying to switch to use native versioning so we can do bulk imports and still get versions.

I set the native_versioning option:

make_versioned(
    options={'native_versioning': True}
)

And then ran an alembic migration calling sync_trigger for all the version tables:

conn = op.get_bind()
sync_trigger(conn, 'user_version')
sync_trigger(conn, 'inventory_item_version')
...

I can see in the database that _audit triggers are created for all the tables. However, my tests now always return a length of 0 for list(model.versions) after updating a model. Am I missing a step?

[evrys]

After digging a bit deeper, I think this is the same issue as #114. The trigger for making version rows starts like this:

    BEGIN
        transaction_id_value = (SELECT id FROM temporary_transaction);
    EXCEPTION WHEN others THEN
        RETURN NEW;
    END;
    IF transaction_id_value IS NULL THEN
        RETURN NEW;
    END IF;

However, temporary_transaction doesn't exist, so it fails silently and never records the version.

[evrys]

I ended up getting it to work by recreating the transaction table with native_versioning enabled and then copying all the old transactions across. I also needed to enable the hstore extension, and fork sqlalchemy-continuum to change a boolean so it no longer assumed the presence of PropertyModTrackerPlugin.

I should make a PR adding some documentation about it and fixing the mod tracker thing. In the meantime this is the migration I used, if someone else encounters this issue:

conn = op.get_bind()

# Enable hstore extension (used by versioning triggers)
op.execute("CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public")

# Rename our existing transaction table out of the way
op.execute("ALTER TABLE transaction RENAME CONSTRAINT transaction_pkey TO old_transaction_pkey")
op.execute("ALTER TABLE transaction RENAME CONSTRAINT transaction_user_id_fkey TO old_transaction_user_id_fkey")
op.execute("ALTER INDEX ix_transaction_user_id RENAME TO old_ix_transaction_user_id")
op.execute("ALTER TABLE transaction RENAME TO old_transaction;")
op.execute("ALTER SEQUENCE transaction_id_seq RENAME TO old_transaction_id_seq;")

# Make a new transaction table (this adds a particular trigger needed for
# native_versioning to work)
Transaction = db.Model._decl_class_registry.get('Transaction')
Transaction.__table__.create(bind=conn)

# Copy our data across
op.execute("INSERT INTO transaction SELECT * FROM old_transaction;")
op.execute("DROP TABLE old_transaction;")

# Restore the old id sequence
op.execute("DROP SEQUENCE transaction_id_seq;")
op.execute("ALTER SEQUENCE old_transaction_id_seq RENAME TO transaction_id_seq;")

# Create all the versioning update triggers
sync_trigger(conn, 'user_version')
sync_trigger(conn, 'inventory_item_version')
...

[nanvel]

I run in the exact same issue, great that you figured out how to handle the temp transaction table issue. For the _mod issue I created a small PR: https://github.com/kvesteri/sqlalchemy-continuum/pull/273

There is a function uses_property_mod_tracking(manager), but we can not use it inside migration (as plugins set can change and it will change migrations), so I just added kwargs to sync_triggers.

[nanvel]

Was able to make it work by adding transaction_trigger to the existing transaction table:

conn = op.get_bind()

# Enable hstore extension (used by versioning triggers)
op.execute("CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public")

op.execute("""
CREATE OR REPLACE FUNCTION transaction_temp_table_generator()
RETURNS TRIGGER AS $$
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS
        temporary_transaction (id BIGINT, PRIMARY KEY(id))
    ON COMMIT DELETE ROWS;
    INSERT INTO temporary_transaction (id) VALUES (NEW.id);
    RETURN NEW;
END;
$$
LANGUAGE plpgsql
""")

op.execute("""
CREATE TRIGGER transaction_trigger
AFTER INSERT ON transaction
FOR EACH ROW EXECUTE PROCEDURE transaction_temp_table_generator()
""")

sync_trigger(conn, "mytable_version")

downgrade:

conn = op.get_bind()
drop_trigger(conn, "mytable")

op.execute("DROP TRIGGER transaction_trigger ON transaction;")
op.execute("DROP FUNCTION transaction_temp_table_generator();")

[guzzijones]

Postgres database server version 14 also requires quotes around the version_table_name_format if your table name has capital letters.

sync_trigger(ob.get_bind(), 'mytable_version',
    use_property_mod_tracking=False,
   version_table_name_format="\"%s_version\"")