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

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
Thread:
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?

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nicolas Barbier 2011-03-09 10:54:48 Re: What happens If a table changes during a query/procedure execution
Previous Message Yeb Havinga 2011-03-09 08:14:48 Re: Sync Rep and shutdown Re: Sync Rep v19