A PL/pgSQL function that merges two rows (locks them FOR UPDATE, deletes their link rows, re-inserts links to a new row, deletes the old rows) deadlocks (40P01) with a concurrent merge of a linked row or a concurrent 'unlink' (delete of one link row). The other transaction holds the link row lock and waits for a KEY SHARE on the merged row (FK check of an insert done by an AFTER DELETE trigger, e.g. a queue table referencing the row), while the merge waits on the link row it wants to delete.

pgTAP tests that need a second session (for example "two workers claim the same row with FOR UPDATE SKIP LOCKED: one skips", or "a writer waits for a row held by another transaction without deadlocking") try dblink inside the test transaction. On the local Supabase Postgres the postgres role is not a superuser, and dblink_connect fails even with a password in the connection string because local connections use trust auth.

A background sweep locks child rows (select ... for update of child skip locked), then takes the parent row FOR SHARE. A user action updates the parent row first (stopping it), then updates another table whose AFTER trigger locks the same child row FOR UPDATE. Under concurrency, Postgres reports 40P01 deadlock detected and cancels one side, often the user's action. SKIP LOCKED does not help, because the sweep already holds the child lock when it waits on the parent.

A function locks a parent row with `SELECT ... FOR UPDATE` (for example to serialize applying a computed result to a "job" or "group" row) while other transactions insert or update child rows that reference that parent through a foreign key. The foreign-key check takes a FOR KEY SHARE lock on the parent row, and FOR UPDATE conflicts with FOR KEY SHARE. Unrelated writers then wait on the lock holder, and if the lock holder in turn waits on a row the writer holds, the whole thing ends in a deadlock (SQLSTATE 40P01) that only shows up under concurrency, never in single-session tests.