ptrdom commented on code in PR #515: URL: https://github.com/apache/pekko-persistence-r2dbc/pull/515#discussion_r4423051844
########## ddl-scripts/migration/snapshot_start_from_snapshot_postgres.sql: ########## @@ -0,0 +1,13 @@ +-- Adds the snapshot columns and index that are needed by the queries that use snapshots as starting points +-- (eventsBySlicesStartingFromSnapshots, eventsByPersistenceIdStartingFromSnapshot and their current variants). +-- Use this for both the BYTEA and the JSONB schema. See the migration guide for a rolling update procedure. + +ALTER TABLE snapshot ADD COLUMN IF NOT EXISTS db_timestamp timestamp with time zone; +ALTER TABLE snapshot ADD COLUMN IF NOT EXISTS tags TEXT ARRAY; + +-- populate the new columns from the corresponding events +UPDATE snapshot s SET db_timestamp = e.db_timestamp, tags = e.tags + FROM event_journal e + WHERE s.persistence_id = e.persistence_id AND s.seq_nr = e.seq_nr; Review Comment: I put the fallback and a test for the migration scripts on a branch, so you can cherry-pick it if it looks right: https://github.com/ptrdom/pekko-persistence-r2dbc/commit/7a9b6e3761ee76a0c177314c0f492a931563e69f (`git cherry-pick 7a9b6e3` after fetching `snapshot-migration-script-spec` from my fork). - The Postgres, Yugabyte and MySQL scripts set `db_timestamp` from `write_timestamp` for the snapshots that are still NULL after the join. For Postgres and Yugabyte I used `TIMESTAMP WITH TIME ZONE 'epoch' + write_timestamp * INTERVAL '1 millisecond'` instead of the `to_timestamp(... / 1000.0)` I suggested above, because it's exact to the millisecond. MySQL uses `FROM_UNIXTIME(write_timestamp / 1000)`. - The migration guide shows the second update and explains it. - New `StartFromSnapshotMigrationScriptSpec`, since the scripts aren't run anywhere in CI. It creates a legacy snapshot table and writes three entities with the snapshot plugin disabled: one whose event exists (with a tag), one after `deleteEventsTo(snapshotSeqNr)` and one after deleting past the snapshot. It then runs the dialect's script from `ddl-scripts/migration/` on that table and checks that `currentEventsBySlicesStartingFromSnapshots` emits all three. The first must have the event's timestamp and tags; the other two must have the write timestamp and no tags. It's cancelled on H2, which has no script. It passes locally on PostgreSQL, MySQL, MariaDB and Yugabyte. Without the script change it fails on PostgreSQL and MySQL, and only the snapshot whose event exists is emitted. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
