COMMIT within function?

Поиск
Список
Период
Сортировка
От Dawid Kuroczko
Тема COMMIT within function?
Дата
Msg-id 758d5e7f04112110292b4cc916@mail.gmail.com
обсуждение исходный текст
Ответы Re: COMMIT within function?  (Pierre-Frédéric Caillaud<lists@boutiquenumerique.com>)
Re: COMMIT within function?  (Michael Fuhr <mike@fuhr.org>)
Список 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 по дате отправления:

Предыдущее
От: Chris Green
Дата:
Сообщение: Any good report/form generators for postgresql?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Wrong string length from unicode database in Borland's app