Lists: | pgsql-hackers |
---|
From: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | What happens If a table changes during a query/procedure execution |
Date: | 2011-03-09 10:00:09 |
Message-ID: | 4D774FA9.6070905@dc.baikal.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Let there are two transactions that were created with read commited
isolation level. In the first one we're executing a SELECT query:
SELECT * FROM t UNION ALL SELECT * FROM t;
In the second transaction we're modifying the same table:
INSERT INTO t DEFAULT VALUES;
COMMIT;
Is it possible that the last UNION part in the first query will retrieve
not the same rows as the first one?
Another scenario is where we're executing two SELECT queries in a stored
procedure:
BEGIN
...
SELECT * FROM t;
SELECT * FROM t;
END;
Is it possible to get different results in the second query? Does SQL
standard define the behaviour in such cases?
From: | Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> |
---|---|
To: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: What happens If a table changes during a query/procedure execution |
Date: | 2011-03-09 10:54:48 |
Message-ID: | AANLkTimEoXT-DYwRF8uhQMJQyuP=vPS925S+_LNtxH9Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2011/3/9 Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>:
> Let there are two transactions that were created with read commited
> isolation level. In the first one we're executing a SELECT query:
> SELECT * FROM t UNION ALL SELECT * FROM t;
>
> In the second transaction we're modifying the same table:
> INSERT INTO t DEFAULT VALUES;
> COMMIT;
>
> Is it possible that the last UNION part in the first query will retrieve not
> the same rows as the first one?
No, because statements never see changes made by other transactions
while they are in flight.
> Another scenario is where we're executing two SELECT queries in a stored
> procedure:
> BEGIN
> ...
> SELECT * FROM t;
> SELECT * FROM t;
> END;
>
> Is it possible to get different results in the second query?
Yes, because they are separate statements, and in READ COMMITTED mode,
a new snapshot is taken when a statement starts. See:
<URL:http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED>.
> Does SQL standard define the behaviour in such cases?
The first one certainly. The standard doesn't describe PL/PgSQL, so
the question is moot in the second case; nonetheless, I assume that
the answer would be yes in the case of SQL/PSM.
Note that the standard defines things that must never happen in the
case of READ COMMITTED, it does not specify that one *must* be able to
see the stuff as committed by previous transactions, for example.
Nicolas
From: | Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> |
---|---|
To: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: What happens If a table changes during a query/procedure execution |
Date: | 2011-03-09 10:57:45 |
Message-ID: | AANLkTikyxrn8FVe38VMnO-wtX5otDFypbcMZMSoZ4xLH@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2011/3/9 Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>:
> Note that the standard defines things that must never happen in the
> case of READ COMMITTED, it does not specify that one *must* be able to
> see the stuff as committed by previous transactions, for example.
Hmm, make that "stuff as committed by concurrent transactions that
committed prior to our statement's execution".
Nicolas
From: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
---|---|
To: | Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: What happens If a table changes during a query/procedure execution |
Date: | 2011-03-09 11:12:53 |
Message-ID: | 4D7760B5.20804@dc.baikal.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
09.03.2011 18:54, Nicolas Barbier:
> 2011/3/9 Vlad Arkhipov<arhipov(at)dc(dot)baikal(dot)ru>:
>
>
>> Let there are two transactions that were created with read commited
>> isolation level. In the first one we're executing a SELECT query:
>> SELECT * FROM t UNION ALL SELECT * FROM t;
>>
>> In the second transaction we're modifying the same table:
>> INSERT INTO t DEFAULT VALUES;
>> COMMIT;
>>
>> Is it possible that the last UNION part in the first query will retrieve not
>> the same rows as the first one?
>>
> No, because statements never see changes made by other transactions
> while they are in flight.
>
>
Is it also true if a statement contains subqueries or function calls?
For instance,
CREATE FUNCTION f() RETURNS NUMERIC AS $$
BEGIN
RETURN (SELECT SUM(a) FROM t);
END;
$$ LANGUAGE 'plpgsql';
SELECT a, f() FROM t;
or
SELECT a, (SELECT SUM(a) FROM t) FROM t;
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
Cc: | Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: What happens If a table changes during a query/procedure execution |
Date: | 2011-03-09 11:15:16 |
Message-ID: | AANLkTinzDh+ev25mWFE5gDEyg-cuqXaZkduftF5nPfSe@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2011/3/9 Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>:
> 09.03.2011 18:54, Nicolas Barbier:
>>
>> 2011/3/9 Vlad Arkhipov<arhipov(at)dc(dot)baikal(dot)ru>:
>>
>>
>>>
>>> Let there are two transactions that were created with read commited
>>> isolation level. In the first one we're executing a SELECT query:
>>> SELECT * FROM t UNION ALL SELECT * FROM t;
>>>
>>> In the second transaction we're modifying the same table:
>>> INSERT INTO t DEFAULT VALUES;
>>> COMMIT;
>>>
>>> Is it possible that the last UNION part in the first query will retrieve
>>> not
>>> the same rows as the first one?
>>>
>>
>> No, because statements never see changes made by other transactions
>> while they are in flight.
>>
>>
>
> Is it also true if a statement contains subqueries or function calls? For
> instance,
>
> CREATE FUNCTION f() RETURNS NUMERIC AS $$
> BEGIN
> RETURN (SELECT SUM(a) FROM t);
> END;
> $$ LANGUAGE 'plpgsql';
>
> SELECT a, f() FROM t;
>
> or
>
> SELECT a, (SELECT SUM(a) FROM t) FROM t;
yes, it is same
Regards
Pavel Stehule
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>