Re: Question about isolation

On Wed, 28 Jan 2004, Samuel Tardieu wrote:
> If in a transaction I call an embedded function in Pl/PgSQL, in which
> I have:
>
>   delete from t where condition;
>   for e in select distinct on (f) * from t where ... loop
>      ...
>   end loop;
>
> Do I have the guarantee that, in any event, rows deleted from table t
> by the delete won't reappear in the select result?

i do not think you have that guarantee in READ COMMITTED mode because
there is a slight possibility another backend sneaked a committed insert in
between the delete and select statement. perhaps you want to
change to SERIALIZABLE transaction isolation. or perhaps you would
like to repeat the WHERE condition from the DELETE in the following
SELECT so as to not gather any of the offending rows.

http://www.postgresql.org/docs/7.4/static/sql-set-transaction.html