removeDeletedShadowRows method

@override String removeDeletedShadowRows(QualifiedTablename oplog, QualifiedTablename shadow)

Deletes any shadow rows where the last oplog operation was a DELETE

Implementation

@override
String removeDeletedShadowRows(
  QualifiedTablename oplog,
  QualifiedTablename shadow,
) {
  // We do an inner join in a CTE instead of a `WHERE EXISTS (...)`
  // since this is not reliant on re-executing a query
  // for every row in the shadow table, but uses a PK join instead.
  return '''
    WITH
      _relevant_shadows AS (
        SELECT DISTINCT ON (s.rowid)
          s.rowid AS rowid,
          op.optype AS last_optype
        FROM $oplog AS op
        INNER JOIN $shadow AS s
        ON s.namespace = op.namespace
          AND s.tablename = op.tablename
          AND s."primaryKey"::jsonb = op."primaryKey"::jsonb
        WHERE op.timestamp = \$1
        ORDER BY s.rowid, op.rowid DESC
      ),
      _to_be_deleted AS (
        SELECT rowid FROM _relevant_shadows WHERE last_optype = 'DELETE'
      )
    DELETE FROM $shadow
    WHERE rowid IN (SELECT rowid FROM _to_be_deleted);
  ''';
}