To: pgsql-sql@postgresql.org
From: Samuel Tardieu <sam@rfc1149.net>
Subject: Re: Question about isolation
Date: Wed, 28 Jan 2004 22:45:13 +0100
Organization: Avian Carrier & Friends
Lines: 34
Message-ID: <871xpj928m.fsf@beeblebrox.enst.fr>
References: <Pine.BSO.4.44.0401281507300.30592-100000@detroit.arbor.net>
	<Pine.BSO.4.44.0401281534400.30592-100000@detroit.arbor.net>
Mime-Version: 1.0 (generated by SEMI 1.14.5 - "Awara-Onsen")
Content-Type: text/plain; charset=US-ASCII
Gmane-NNTP-Posting-Host: willow.rfc1149.net
User-Agent: T-gnus/6.16.2 (based on Gnus v5.10.2) (revision 02) SEMI/1.14.5
	(Awara-Onsen) FLIM/1.14.5 (Demachiyanagi) APEL/10.6 Emacs/21.3
	(i386--freebsd) MULE/5.0 (SAKAKI)
Cancel-Lock: sha1:jo8lnzwyx7yJhNhnzob9wwJ5dkI=

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