pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about isolation
5+ messages / 3 participants
[nested] [flat]
* Question about isolation
@ 2004-01-28 17:58 Samuel Tardieu <sam@rfc1149.net>
2004-01-28 20:12 ` Re: Question about isolation Chester Kustarz <chester@arbor.net>
0 siblings, 1 reply; 5+ messages in thread
From: Samuel Tardieu @ 2004-01-28 17:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question about isolation
2004-01-28 17:58 Question about isolation Samuel Tardieu <sam@rfc1149.net>
@ 2004-01-28 20:12 ` Chester Kustarz <chester@arbor.net>
2004-01-28 20:36 ` Re: Question about isolation Chester Kustarz <chester@arbor.net>
0 siblings, 1 reply; 5+ messages in thread
From: Chester Kustarz @ 2004-01-28 20:12 UTC (permalink / raw)
To: Samuel Tardieu <sam@rfc1149.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question about isolation
2004-01-28 17:58 Question about isolation Samuel Tardieu <sam@rfc1149.net>
2004-01-28 20:12 ` Re: Question about isolation Chester Kustarz <chester@arbor.net>
@ 2004-01-28 20:36 ` Chester Kustarz <chester@arbor.net>
2004-01-28 21:45 ` Re: Question about isolation Samuel Tardieu <sam@rfc1149.net>
0 siblings, 1 reply; 5+ messages in thread
From: Chester Kustarz @ 2004-01-28 20:36 UTC (permalink / raw)
To: Samuel Tardieu <sam@rfc1149.net>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question about isolation
2004-01-28 17:58 Question about isolation Samuel Tardieu <sam@rfc1149.net>
2004-01-28 20:12 ` Re: Question about isolation Chester Kustarz <chester@arbor.net>
2004-01-28 20:36 ` Re: Question about isolation Chester Kustarz <chester@arbor.net>
@ 2004-01-28 21:45 ` Samuel Tardieu <sam@rfc1149.net>
2004-01-28 23:03 ` Re: Question about isolation Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Samuel Tardieu @ 2004-01-28 21:45 UTC (permalink / raw)
To: pgsql-sql
>>>>> "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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question about isolation
2004-01-28 17:58 Question about isolation Samuel Tardieu <sam@rfc1149.net>
2004-01-28 20:12 ` Re: Question about isolation Chester Kustarz <chester@arbor.net>
2004-01-28 20:36 ` Re: Question about isolation Chester Kustarz <chester@arbor.net>
2004-01-28 21:45 ` Re: Question about isolation Samuel Tardieu <sam@rfc1149.net>
@ 2004-01-28 23:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-01-28 23:03 UTC (permalink / raw)
To: Samuel Tardieu <sam@rfc1149.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-01-28 23:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-28 17:58 Question about isolation Samuel Tardieu <sam@rfc1149.net>
2004-01-28 20:12 ` Chester Kustarz <chester@arbor.net>
2004-01-28 20:36 ` Chester Kustarz <chester@arbor.net>
2004-01-28 21:45 ` Samuel Tardieu <sam@rfc1149.net>
2004-01-28 23:03 ` Tom Lane <tgl@sss.pgh.pa.us>
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