Row Lock

CREATE OR REPLACE FUNCTION process_queue() RETURNS VOID AS $$
DECLARE
    message RECORD;
BEGIN
    LOOP
        -- Get the next message in the queue, locking the row for update to avoid race conditions
        SELECT *
        INTO message
        FROM message_queue
        WHERE status = 'queued'
        ORDER BY created_at
        FOR UPDATE
        SKIP LOCKED
        LIMIT 1;

        -- If there are no more messages in the queue, exit the loop
        IF NOT FOUND THEN
            EXIT;
        END IF;

        -- Process the message
        -- ...

        -- Update the message status to mark it as processed
        UPDATE message_queue
        SET status = 'processed', processed_at = NOW()
        WHERE id = message.id;

        -- Commit the transaction to release the lock
        COMMIT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;