Re: COMMIT within function?

On Mon, 22 Nov 2004 00:16:07 +0100, Pierre-Frédéric Caillaud
<lists(at)boutiquenumerique(dot)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