COMMIT within function?

From: Dawid Kuroczko <qnex42(at)gmail(dot)com>
To: Pgsql General <pgsql-general(at)postgresql(dot)org>
Subject: COMMIT within function?
Date: 2004-11-21 18:29:26
Message-ID: 758d5e7f04112110292b4cc916@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-11-21 18:52:35 Re: Wrong string length from unicode database in Borland's app
Previous Message Chris Green 2004-11-21 17:50:30 Any good report/form generators for postgresql?