Race condition when removing unused URLs

XMLWordPrintable

      There is a race condition in the delete_unused_url function:

        SELECT ARRAY(
    SELECT id FROM url url_row WHERE id = any(ids)
    EXCEPT
    SELECT url FROM edit_url JOIN edit ON (edit.id = edit_url.edit) WHERE edit.status = 1
    EXCEPT
    SELECT entity1 FROM l_area_url
    EXCEPT
    ...
  ) INTO clear_up;

  DELETE FROM url_gid_redirect WHERE new_id = any(clear_up);
  DELETE FROM url WHERE id = any(clear_up);

      If a reference to an id selected into clear_up is inserted into an l_* table before the DELETE statement is run, then an exception will occur:

      DBD::Pg::db commit failed: ERROR:  update or delete on table "url" violates foreign key constraint "l_release_url_fk_entity1" on table "l_release_url"
DETAIL:  Key (id)=(11701312) is still referenced from table "l_release_url".
CONTEXT:  SQL statement "DELETE FROM url WHERE id = any(clear_up)"
PL/pgSQL function delete_unused_url(integer[]) line 42 at SQL statement
PL/pgSQL function remove_unused_url() line 8 at EXECUTE

            Assignee:
            Michael Wiencek
            Reporter:
            Michael Wiencek
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:

                Version Package
                Schema change, 2025 Q2