Minimise use of dtr index scans for pg transaction ordering

nevella / alcina

Automatically exported from code.google.com/p/alcina

1 stars 2 forks source link

Minimise use of dtr index scans for pg transaction ordering #13

Open nevella opened 2 years ago

nevella commented 2 years ago

Sketch (and logic):

Goal is to apply pg transactions to memory graph in pg commit order
Clustered implementation:
- All transactions follow precTx (wall time that precommit message that dtr with persistent id x is in a pg update transaction is committed to kafka) < cTx (wall time that transaction is commited to pg) <postcTx (wall time that postcommit message is committed to kafka)
- For argument, transactions T1, T2 have been committed in numeric order
- Await kafka message that either of those transactions have been committed (message is possibly not in commit order)
- precT1 < cT1 < cT2 < postT2 - so we will know to check the ids of T1 and T2 need to be checked for xmin value
- on app startup, scan for requests with null commit time (using index). mark as check-for-commit (since system may miss precommit kafka notifications)
- During normal operation, leader cleans null commit time every 10s off thread

nevella commented 2 years ago

Refine:

pre warmpu (within pg snapshot), refreshPositions, add to ignore list (check markHighestVisibleTransformList uses snapshot conn)
post warmup, run ensureTimestamps. Ensure timestamps removes 'skip locked'
then refreshpositions. done!
test: do some updates during a dev server restart (cluster of 2 dev servers). Check visible

nevella commented 2 years ago

Wait until all transactions that can write which are in progress after kafka connect have finished...that's all.
Remove transaction_commit_time, since we now know all outstanding dtr ids (via either kafka or local signals) at all times

nevella commented 2 years ago

TODO: populate transactionCommitTime (for rollingdata - and other bulk transform operations - not needed for domain commit sequencing)

nevella commented 2 years ago

Actually - there's a better way. This is one place (given it's purely db logic) where triggers are better than app-level code. Publication counters may well be another

[ ] Use pg listeners for notification of transactions (they'll be ordered)
[ ] Store dtr commit time in a separate, indexed table - domaintransformrequest_committime
[ ] Update that table via pg triggers. Note one trigger will one during tx (create domaintransformrequest_committime row) - other will one post tx (update dtrcommittime)
[ ] For clustered warmup, simply establishing the listener before snapshot is enough
[ ] For pg (listener) connection loss, update domaintransformrequest_committime.dtrcommittime via jdbc before reconnection