Skip to content
Snippets Groups Projects
  • Sean Quah's avatar
    88227700
    Fix background updates failing to add unique indexes on receipts (#14453) · 88227700
    Sean Quah authored
    
    As part of the database migration to support threaded receipts, there is
    a possible window in between
    `73/08thread_receipts_non_null.sql.postgres` removing the original
    unique constraints on `receipts_linearized` and `receipts_graph` and the
    `reeipts_linearized_unique_index` and `receipts_graph_unique_index`
    background updates from `72/08thread_receipts.sql` completing where
    the unique constraints on `receipts_linearized` and `receipts_graph` are
    missing. Any emulated upserts on these tables must therefore be
    performed with a lock held, otherwise duplicate rows can end up in the
    tables when there are concurrent emulated upserts. Fix the missing lock.
    
    Note that emulated upserts no longer happen by default on sqlite, since
    the minimum supported version of sqlite supports native upserts by
    default now.
    
    Finally, clean up any duplicate receipts that may have crept in before
    trying to create the `receipts_graph_unique_index` and
    `receipts_linearized_unique_index` unique indexes.
    
    Signed-off-by: default avatarSean Quah <seanq@matrix.org>
    Fix background updates failing to add unique indexes on receipts (#14453)
    Sean Quah authored
    
    As part of the database migration to support threaded receipts, there is
    a possible window in between
    `73/08thread_receipts_non_null.sql.postgres` removing the original
    unique constraints on `receipts_linearized` and `receipts_graph` and the
    `reeipts_linearized_unique_index` and `receipts_graph_unique_index`
    background updates from `72/08thread_receipts.sql` completing where
    the unique constraints on `receipts_linearized` and `receipts_graph` are
    missing. Any emulated upserts on these tables must therefore be
    performed with a lock held, otherwise duplicate rows can end up in the
    tables when there are concurrent emulated upserts. Fix the missing lock.
    
    Note that emulated upserts no longer happen by default on sqlite, since
    the minimum supported version of sqlite supports native upserts by
    default now.
    
    Finally, clean up any duplicate receipts that may have crept in before
    trying to create the `receipts_graph_unique_index` and
    `receipts_linearized_unique_index` unique indexes.
    
    Signed-off-by: default avatarSean Quah <seanq@matrix.org>