Re: foreign key on delete cascade order?

[Michael Lewis <mlewis@xxxxxxxxxxx>]

We are updating the entire status table every 5 minutes with

BEGIN;

UPDATE status SET () WHERE pollid = $1;

COMMIT;

The issue is arriving when some does a DELETE during the UPDATE of status

DELETE FROM mach WHERE machid=$1;

Could you set lock_timeout, lock table explicitly for SHARE UPDATE EXCLUSIVE (pretty sure that would be the proper level), then retry if it fails because a delete is already going on?

Also, are you confident that before you call 'begin' to do the update, you are not already in a transaction which might have some lock on row(s) in mach, or one of the other tables involved?