successive select statements

Started by Sahagian, Davidover 14 years ago2 messagesgeneral
Jump to latest
#1Sahagian, David
david.sahagian@emc.com

In postgresql.org/docs/9.1/static/transaction-iso.html I read

13.2.1. Read Committed Isolation Level
. . . two successive SELECT commands can see different data, even though they are within a single transaction . . .

Please consider this code being executed by postgres:
= = = = = = = = = =
select some_int from tableX
union all
select another_int from tableY
;
= = = = = = = = = =
Are these two select statements considered "successive" when isolation == Read Committed ?
Or are they instead guaranteed to see the same snapshot of the database ?

Also, please consider this code being executed by postgres:
= = = = = = = = = =
CREATE Or Replace FUNCTION fx_one() RETURNS setof integer AS
$$
select some_int from tableX;
$$ LANGUAGE 'sql';

CREATE Or Replace FUNCTION fx_two() RETURNS setof integer AS
$$
select another_int from tableY;
$$ LANGUAGE 'sql';

select * from fx_one()
union all
select * from fx_two()
;
= = = = = = = = = =
Are the two select statements inside fx_one and fx_two considered "successive" when isolation == Read Committed ?
Or are they instead guaranteed to see the same snapshot of the database ?

Thanks,
-dvs-

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sahagian, David (#1)
Re: successive select statements

<david.sahagian@emc.com> writes:

In postgresql.org/docs/9.1/static/transaction-iso.html I read
13.2.1. Read Committed Isolation Level
. . . two successive SELECT commands can see different data, even though they are within a single transaction . . .

Please consider this code being executed by postgres:
= = = = = = = = = =
select some_int from tableX
union all
select another_int from tableY
;
= = = = = = = = = =
Are these two select statements considered "successive" when isolation == Read Committed ?

No, the UNION is a single command. The fact that the word "SELECT"
occurs twice within it is a SQL syntactic artifact --- it doesn't
convert it into two commands.

regards, tom lane