Re: Question about isolation

>>>>> "Chester" == Chester Kustarz <chester(at)arbor(dot)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(at)rfc1149(dot)net -- http://www.rfc1149.net/sam