What happens If a table changes during a query/procedure execution

2011/3/9 Vlad Arkhipov <arhipov@dc.baikal.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&gt;.

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

2011/3/9 Nicolas Barbier <nicolas.barbier@gmail.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

09.03.2011 18:54, Nicolas Barbier:

2011/3/9 Vlad Arkhipov<arhipov@dc.baikal.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;

2011/3/9 Vlad Arkhipov <arhipov@dc.baikal.ru>:

09.03.2011 18:54, Nicolas Barbier:

2011/3/9 Vlad Arkhipov<arhipov@dc.baikal.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