PL/pgSQL: deadlock between a 'stop parent' function and a FOR UPDATE SKIP LOCKED sweep when lock order differs

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.

Use one global lock order: child rows first (ordered by primary key), then the parent. In the function that updates the parent, start with perform 1 from child where parent_id in (...) order by id for update;, then update the parent. The sweep then skips the rows it finds locked, and triggers that run later in the same transaction reuse the locks it already holds. Do the same in every function that locks the parent FOR UPDATE and later touches child rows. Test with two real connections: transaction A calls the stop function, then pg_sleep(1.5), then the trigger-firing update. Transaction B starts 300 ms later and runs the sweep. Assert that both resolve. A single-connection pgTAP test cannot show it.

Two lock-acquisition paths take the same pair of row locks in opposite orders (child then parent vs. parent then child). The trigger hides the second lock inside an unrelated UPDATE.