Re: Question about isolation

On Wed, 28 Jan 2004, Chester Kustarz wrote:
> 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

perhaps the isolation level applies to the statement that called the
function, in which case you would be ok. that would make more sense, no?