delete inside for plpgsql loop on same relation?

Поиск
Список
Период
Сортировка
Искать

delete inside for plpgsql loop on same relation?

От:
Rob Nikander <rob.nikander@gmail.com>
Дата:
Hi,

Are there guarantees about how this plpgsql behaves? It’s deleting from a table while it loops over it. So far it seems like the delete is logically after the select, as I hoped, and doesn’t interfere. 

    for row in select * from some_stuff loop
        delete from some_stuff where …
        ...
    end loop;

I’m using a temporary table of “things to process” and looping over it, deleting the ones as I go.

I don’t see anything mentioned here: https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

Rob

Re: delete inside for plpgsql loop on same relation?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Rob Nikander  writes:
> Are there guarantees about how this plpgsql behaves? It’s deleting from a table while it loops over it. So far it seems like the delete is logically after the select, as I hoped, and doesn’t interfere. 

>     for row in select * from some_stuff loop
>         delete from some_stuff where …
>         ...
>     end loop;

Sure.  A query will not see the effects of queries that start after it.
This isn't particularly plpgsql-specific.

Some qualifications are needed if you have triggers or volatile functions
in the first query (those *can* see later effects); but this usage seems
safe enough.

			regards, tom lane
FAQ