Concurrent PostgreSQL and SQLite3 clients can trigger a deadlock in electric.reorder_main_op pgSQL, Replication.Postgres.Writer restarts, writes being replicated appear to be lost

[nurturenature]

Concurrent PostgreSQL and SQLite3 clients can trigger a deadlock:

• error in electric.reorder_main_op pgSQL
• error in Replication.Postgres.Writer genserver which terminates and restarts
• replication transaction appears to not be retried
• previously OK'd write on a client appears to be dropped and not replicated

Example:

• 2 PostgreSQL clients
• 3 SQLite3 clients
• write by a SQLite3 client confirmed Ok
• values only ever read by that same client(twice)
• values only ever appear in that same client's replication logs
• errors in ElectricSQL and PostgreSQL logs

Error with [key,value] of [23,111]:

Test log:

;; client process 5 writes [23,111] and reads it twice
;; no observations of [23,111] on any other client
 
5 :ok :txn [[:w 29 49] [:w 23 111] [:r 29 49] [:r 29 49]]
...
5 :ok :txn [[:r 23 111]]
...
5 :ok :txn [[:w 22 252] [:r 23 111] [:w 5 192]]

ElectricSQL sync service log:

pid=<0.3300.0> origin=postgres_1 [error] GenServer #PID<0.3300.0> terminating
** (RuntimeError) Postgres.Writer failed to execute statement INSERT INTO "public"."lww_registers"("k","v") VALUES (23,111) with error {:error, {:error, :error, "40P01", :deadlock_detected, "deadlock detected", [detail: "Process 795 waits for ShareLock on transaction 4162; blocked by process 794.\nProcess 794 waits for ShareLock on transaction 4161; blocked by process 795.", file: "deadlock.c", hint: "See server log for query details.", line: "1130", routine: "DeadLockReport", severity: "ERROR", where: "while inserting index tuple (7,19) in relation \"shadow__public__lww_registers\"\nSQL statement \"INSERT INTO electric.shadow__public__lww_registers (_currently_reordering, k, __reordered_v)\n                VALUES (true, NEW.k, NEW.v)\n                ON CONFLICT (k) DO UPDATE SET\n                    _currently_reordering = true, __reordered_v = NEW.v\n                RETURNING *\"\nPL/pgSQL function electric.reorder_main_op___public__lww_registers() line 12 at SQL statement"]}}
    (electric 0.9.0) lib/electric/replication/postgres/writer.ex:93: anonymous fn/2 in Electric.Replication.Postgres.Writer.send_transaction/3
    (elixir 1.15.7) lib/enum.ex:984: Enum."-each/2-lists^foreach/1-0-"/2
    (epgsql 4.7.1) /root/electricsql/components/electric/deps/epgsql/src/epgsql.erl:458: :epgsql.with_transaction/3
    (electric 0.9.0) lib/electric/replication/postgres/writer.ex:86: Electric.Replication.Postgres.Writer.send_transaction/3
    (electric 0.9.0) lib/electric/replication/postgres/writer.ex:67: anonymous fn/2 in Electric.Replication.Postgres.Writer.handle_events/3
    (elixir 1.15.7) lib/enum.ex:2510: Enum."-reduce/3-lists^foldl/2-0-"/3
    (electric 0.9.0) lib/electric/replication/postgres/writer.ex:66: Electric.Replication.Postgres.Writer.handle_events/3
    (gen_stage 1.2.1) lib/gen_stage.ex:2578: GenStage.consumer_dispatch/6

PostgreSQL log:

ERROR: deadlock detected
DETAIL: Process 795 waits for ShareLock on transaction 4162; blocked by process 794.
 Process 794 waits for ShareLock on transaction 4161; blocked by process 795.
 Process 795: INSERT INTO "public"."lww_registers"("k","v") VALUES (23,111)
 Process 794: INSERT INTO lww_registers (k,v) VALUES (29,172) ON CONFLICT(k) DO UPDATE SET v = 172
CONTEXT: while inserting index tuple (7,19) in relation "shadow__public__lww_registers"
 SQL statement "INSERT INTO electric.shadow__public__lww_registers (_currently_reordering, k, __reordered_v)
                 VALUES (true, NEW.k, NEW.v)
                 ON CONFLICT (k) DO UPDATE SET
                     _currently_reordering = true, __reordered_v = NEW.v
                 RETURNING *"
 PL/pgSQL function electric.reorder_main_op___public__lww_registers() line 12 at SQL statement
STATEMENT: INSERT INTO "public"."lww_registers"("k","v") VALUES (23,111)

SQLite3 client log (only appears in log of client that did the write):

[proto] send: ... new: ["23", "111"], old: data: ["23", "110"]
...
[proto] send: ... new: ["23", "119"], old: data: ["23", "111"]

[linear]

VAX-1618 Concurrent PostgreSQL and SQLite3 clients can trigger a deadlock in electric.reorder_main_op pgSQL, Replication.Postgres.Writer restarts, writes being replicated appear to be lost