Thread: Question about isolation

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?
 Sam
-- 
Samuel Tardieu -- sam@rfc1149.net -- http://www.rfc1149.net/sam

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

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?

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

Samuel Tardieu <sam@rfc1149.net> writes:
>>> 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.

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

Hmm.  I think you need to look closer.  AFAIR the READ COMMITTED
behavior is only an issue if you give the commands interactively
from the client.  Inside a plpgsql function we do not do
SetQuerySnapshot() and therefore the snapshot of other transactions'
effects does not advance.  So I think the coding should be safe
... at the moment.  (A number of people think the lack of
SetQuerySnapshot inside functions is a bug; so the behavior might
change in future.)

Using SERIALIZABLE mode would probably make your code more future-proof,
but if you are presently seeing failures, there's some other effect
involved here.
        regards, tom lane