# creates a uniquely-named file
new_path = do_work()

old_path = database.query('select old path')
unlink(old_path)

database.query('insert new path')

CREATE TABLE paths (
    used_flag boolean not null default 'f',
    when_entered timestamptz not null default current_timestamp,
    data text not null
);

INSERT INTO paths (data) VALUES
('aa'),('bb'),('cc'),('dd');

SESSION 1             SESSION2         SESSION 3

BEGIN;
                                       BEGIN;

UPDATE      paths
    SET     used_flag = TRUE
    WHERE   used_flag = FALSE
    RETURNING data;

                      BEGIN;

                      INSERT INTO
                      paths(data)
                      VALUES
                      ('ee'),('ff');

                      COMMIT;
                                       UPDATE      paths
                                           SET     used_flag = TRUE
                                           WHERE   used_flag = FALSE
                                           RETURNING data;


                      BEGIN;

                      INSERT INTO
                      paths(data)
                      VALUES
                      ('gg'),('hh');

                      COMMIT;

COMMIT;

UPDATE      paths
    SET     used_flag = TRUE
    WHERE entry_id = (
        SELECT entry_id
        FROM paths
        WHERE used_flag = FALSE
        ORDER BY when_entered
        LIMIT 1
    )
    AND used_flag = FALSE
    RETURNING data;

UPDATE      paths
    SET     used_flag = TRUE
    WHERE entry_id = (
        SELECT min(entry_id)
        FROM paths
        WHERE used_flag = FALSE
    )
    AND used_flag = FALSE
    RETURNING data;

old_path = database.query('
    UPDATE      paths
        SET     used_flag = TRUE
        WHERE   used_flag = FALSE
        RETURNS data');