How to maintain a postgreSQL lock from a trigger "before update" through the update operation itself

Question

I apologize if this is an answered question, I did some research, and I couldn't find an answer.

I'm maintaining a folder/file like structure in my code where I have ordered items that cascade order changes on update and deletion operations. However, these triggers need to both lock rows to ensure that the order changes are completed and continue to lock through the completion of the operation

The updating process is relatively simple. This is the governing pseudo-code for the entire operation:

check if pg_trigger_depth() >= 1
    return because this was a cascaded update from a trigger

lock the table for update on items with the old folder_parent_id
lock the table for update on items with the new folder_parent_id

update the old rows setting order_number -= 1 where the order_number is > the old order_number, and the folder_parent_id is the same as the old one
update the new rows setting order_number +=1 where the order_number is >= the new order_number and the folder_parent_id is the same as the new one

allow the update operation to go through (setting the order_number/folder_parent_id of this row to its new location)


release the lock for update on items with the old folder_parent_id
release the lock for update on items with the new folder_parent_id

If the lock is released before the actual operation goes through, this sort of race condition can happen. In this sample problem, two updates are being called simultaneously:

Given children of a folder: a(0), b(1), c(2), d(3), e(4)

the letters are the identifying properties and the numbers are the order numbers

we want to run these operations: c(2 -> 1), d(3 -> 0)

Here's the timeline for these operations:

BEFORE UPDATE ON c:

decrement everything > OLD c.order_number (d--, e--)
increment everything >= NEW c.order_number (b++, d++, e++)

CURRENT STATE: a(0), b(2), c(2), d(3), e(4)

BEFORE UPDATE ON d:

decrement everything > OLD d.order_number (e--)
increment everything > NEW d.order_number (a++, b++, c++, e++)

CURRENT STATE: a(1), b(3), c(3), d(3), e(4)

SET c = 1

SET d = 0

FINAL STATE: d(0), a(1), c(1), b(3), e(4)

Clearly, the race condition here is the fact that c and d both alter each other's position in the list, but if the before operation trigger runs on each one before the state change happens, then the operations they perform on each other are discarded.

Is there a straightforward way to either make sure that locks are maintained on these tables through from start to finish of this operation, or otherwise to do this in a way that fixes this sort of race condition? I've been considering creating a separate table File_Structure_Lock that would be locked for update in a before trigger, and then unlocked in the after trigger to circumvent the PostgreSQL locking system, but I figured that there had to be a better method.

EDIT: I was asked for the actual SQL. My issue here is in preparation for a refactor on code that was already existing due to that code having race conditions that were causing errors. I can try to mark this up in a minute, but here's the raw code that I'm working with, with a few variable name changes to make it more generally understandable

CREATE OR REPLACE FUNCTION getOrderLock() RETURNS TRIGGER AS $getOrderLock$
BEGIN
    PERFORM * FROM Folders FOR UPDATE;
    PERFORM * FROM Files FOR UPDATE;
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
        RETURN NEW;
        ELSIF (TG_OP = 'DELETE') THEN
        RETURN OLD;
    END IF;
END;
$getOrderLock$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_folder_lock_rows
BEFORE INSERT OR UPDATE OR DELETE ON Folders
FOR EACH STATEMENT
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE getOrderLock();

CREATE TRIGGER trigger_file_lock_rows
BEFORE INSERT OR UPDATE OR DELETE ON Files
FOR EACH STATEMENT
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE getOrderLock();


CREATE OR REPLACE FUNCTION adjust_order_numbers_after_folder_update() RETURNS TRIGGER AS $adjust_order_numbers_after_nav_update$
BEGIN
    --update old location
    UPDATE Folders
        SET order_number = Folders.order_number - 1
        WHERE Folders.order_number >= OLD.order_number
        AND Folders.page_id = OLD.page_id
        AND COALESCE(Folders.folder_parent_id, 0) = COALESCE(OLD.folder_parent_id, 0)
        AND Folders.id != NEW.id;

    UPDATE Files
        SET order_number = Files.order_number - 1
        WHERE Files.order_number >= OLD.order_number
        AND Files.page_id = OLD.page_id
        AND COALESCE(Files.folder_parent_id, 0) = COALESCE(OLD.folder_parent_id, 0);

    --update new location
    UPDATE Folders
        SET order_number = Folders.order_number + 1
        WHERE Folders.order_number >= NEW.order_number
        AND Folders.page_id = NEW.page_id
        AND COALESCE(Folders.folder_parent_id, 0) = COALESCE(NEW.folder_parent_id, 0)
        AND Folders.id != NEW.id;

    UPDATE Files
        SET order_number = Files.order_number + 1
        WHERE Files.order_number >= NEW.order_number
        AND Files.page_id = NEW.page_id
        AND COALESCE(Files.folder_parent_id, 0) = COALESCE(NEW.folder_parent_id, 0);

    RETURN NEW;
END;
$adjust_order_numbers_after_nav_update$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION adjust_order_numbers_after_file_update() RETURNS TRIGGER AS $adjust_order_numbers_after_file_update$
BEGIN

    --update old location
    UPDATE Folders
        SET order_number = Folders.order_number - 1
        WHERE Folders.order_number >= OLD.order_number
        AND Folders.page_id = OLD.page_id
        AND COALESCE(Folders.folder_parent_id, 0) = COALESCE(OLD.folder_parent_id, 0);

    UPDATE Files
        SET order_number = Files.order_number - 1
        WHERE Files.order_number >= OLD.order_number
        AND Files.page_id = OLD.page_id
        AND COALESCE(Files.folder_parent_id, 0) = COALESCE(OLD.folder_parent_id, 0)
        AND Files.id != NEW.id;

    --update new location
    UPDATE Folders
        SET order_number = Folders.order_number + 1
        WHERE Folders.order_number >= NEW.order_number
        AND Folders.page_id = NEW.page_id
        AND COALESCE(Folders.folder_parent_id, 0) = COALESCE(NEW.folder_parent_id, 0);

    UPDATE Files
        SET order_number = Files.order_number + 1
        WHERE Files.order_number >= NEW.order_number
        AND Files.page_id = NEW.page_id
        AND COALESCE(Files.folder_parent_id, 0) = COALESCE(NEW.folder_parent_id, 0)
        AND Files.id != NEW.id;

    RETURN NEW;
END;
$adjust_order_numbers_after_file_update$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_folder_order_shift
AFTER UPDATE ON Folders
FOR EACH ROW 
WHEN (
    (
        COALESCE(OLD.folder_parent_id, 0) != COALESCE(NEW.folder_parent_id, 0)
        OR OLD.order_number != NEW.order_number
        OR Old.page_id != New.page_id
    )
    AND pg_trigger_depth() < 1
)
EXECUTE PROCEDURE adjust_order_numbers_after_folder_update();

CREATE TRIGGER trigger_file_order_shift
AFTER UPDATE ON Files
FOR EACH ROW 
WHEN (
    (
        COALESCE(OLD.folder_parent_id, 0) != COALESCE(NEW.folder_parent_id, 0)
        OR OLD.order_number != NEW.order_number
        OR Old.page_id != New.page_id
    )
    AND pg_trigger_depth() < 1
)
EXECUTE PROCEDURE adjust_order_numbers_after_file_update();

Answer 1

The problem seems to come from the order_number that you insist in being a gap-less sequence of integers ordering the items in each folder. If you want to maintain that, you have to shuffle all items around, and it is indeed hard to do that without some major locking.

But if all you want to do is to maintain a certain order of the items, I would relax the requirement of a gap-less sequence and instead use double precision values to describe the order of items. Then it is easy to insert an item anywhere without changing order_number in any other element – you can always assign the moved item an order_number that is between any two existing ones.