From: Samuel Tardieu <sam@rfc1149.net>
To: pgsql-sql@postgresql.org
Subject: Re: Question about isolation
Date: Wed, 28 Jan 2004 22:45:13 +0100
Message-ID: <871xpj928m.fsf@beeblebrox.enst.fr> (raw)
References: <Pine.BSO.4.44.0401281507300.30592-100000@detroit.arbor.net>
<Pine.BSO.4.44.0401281534400.30592-100000@detroit.arbor.net>
>>>>> "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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sam@rfc1149.net
Subject: Re: Question about isolation
In-Reply-To: <871xpj928m.fsf@beeblebrox.enst.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox