COMMIT within function?
От | Dawid Kuroczko |
---|---|
Тема | COMMIT within function? |
Дата | |
Msg-id | 758d5e7f04112110292b4cc916@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: COMMIT within function?
Re: COMMIT within function? |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: