COMMIT within function?

[Dawid Kuroczko]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Pierre-Frédéric Caillaud]

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:
I suppose you have a good reason to not use a foreign key with "ON DELETE
CASCADE" ?
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;

I don't remember the exact syntax (look in the DELETE docs) but you can
certainly put a left join inside a delete and do it all at once with only
one query, and it'll be faster to boot.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Dawid Kuroczko]

On Mon, 22 Nov 2004 00:16:07 +0100, Pierre-Frédéric Caillaud
<li***@boutiquenumerique.com> wrote:

Suppose I have vacuum_values() function, which removes all
"no longer referenced" by parent column. Kind of function

I suppose you have a good reason to not use a foreign key with "ON DELETE
CASCADE" ?

Well, the issue here is saving space and speed with
lots of repeatable data. Like e-mail addresses, most
of them are frequently reused, so instead of a table

CREATE TABLE messages (author text, ...);

I create two:
CREATE TABLE authors (author_id serial PRIMARY KEY, author text UNIQUE
NOT NULL);
CREATE TABLE messages (author_id integer REFERENCES authors, ...);

....and a matching view, and a function/rule which "invisibly"
changes author to author_id whenever data is added (with
authors table being updated when necessary).

Now, after some time I remove old messages, and some of authors become
"unreferenced" (think: From-s of spam messages). It would be nice to vacuum
them out. The problem is when one of those authors "shows up" after
long absence between our SELECT and actual DELETE. For a busy table
(this happen to be one) it is quite possible. :)

Ah, and ON DELETE CASCADE would mean I would loose perfectly
good messages. Having LOCK on the table is also not-so-good
an idea (think: authors with 2mln rows, messags with 20mln rows).

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;

I don't remember the exact syntax (look in the DELETE docs) but you can
certainly put a left join inside a delete and do it all at once with only
one query, and it'll be faster to boot.

Well, DELETE FROM ... WHERE ... is nice, but it will be explicitly
"all-or-nothing",
whereas with FUNCTION I have a ghost of hope that it may not be atomic. :)
....and I don't think you can do OUTER JOIN without subselect using DELETE FROM
WHERE.

Regards,
Dawid

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Fuhr]

On Sun, Nov 21, 2004 at 07:29:26PM +0100, Dawid Kuroczko wrote:

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.....

In PostgreSQL 8.0, PL/pgSQL functions can trap errors without rolling
back the entire transaction:

http://developer.postgresql.org/docs...ERROR-TRAPPING

BEGIN
DELETE FROM values WHERE value_id = r.value_id;
EXCEPTION
WHEN foreign_key_violation THEN
NULL;
END;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Dawid Kuroczko]

On Sun, 21 Nov 2004 20:10:03 -0700, Michael Fuhr <mi**@fuhr.org> wrote:
http://developer.postgresql.org/docs...ERROR-TRAPPING

BEGIN
DELETE FROM values WHERE value_id = r.value_id;
EXCEPTION
WHEN foreign_key_violation THEN
NULL;
END;

Ahh, exactly what I was looking for. :) The thing I didn't notice
was that, while exception causes rollback to "BEGIN", it does
not mean to the beginning of the function. In other words
I didn't nest BEGIN...END blocks and all I got from using
exceptions was that they did not show any errors. :)

Thank you! I am now enlightened. This works perfect, exactly
as I hoped it would. :)

Regards,
dawid

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html