Re: Question about isolation

>>>>> "Chester" == Chester Kustarz <chester@arbor.net> writes:

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

Yes. But the possible effect your describe (insertion of new rows
after the DELETE statement and before the SELECT) matches accurately
the symptoms we are observing. However, as we do have a lot of
transactions, this is not easy to reproduce.
 Sam
-- 
Samuel Tardieu -- sam@rfc1149.net -- http://www.rfc1149.net/sam