Suppose I have vacuum_values() function, which removes all
"no longer referenced" by parent column. Kind of function
to be run from time to time to clean table from crud.
It looks like this:
CREATE FUNCTION vacuum_values() RETURNS void AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT value_id FROM values NATURAL LEFT JOIN
other_tab WHERE other_tab.value_id IS NULL FOR UPDATE OF values LOOP
DELETE FROM values WHERE value_id = r.value_id;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Here, as the query runs against two table values (2 mln. rows) and
ther_tab (20 mln. rows) it is relatively slow... However there is
a chance that while this query goes, and goes, some rows will become
referenced once more... and the DELETE will fail because of FOREIGN
KEY, and the whole function will ROLLBACK... Is there a way to force
"ignore errors" or something? As far as I checked, I can catch errors,
but I don't really can stop the ROLLBACK. There are SAVEPOINTs but
I guess they are useful for explicit ROLLBACK TO SAVEPOINT...
Of course I can move all this logic outside of backend, and make
the backend just 'do' the DELETEs, ignoring errors... But still,
it should be doable in the procedural languages aswell.....
Regards,
Dawid
