[HACKERS] What happens If a table changes during a query/procedure execution

2011-03-09 Thread Vlad Arkhipov 
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?


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] What happens If a table changes during a query/procedure execution

2011-03-09 Thread Nicolas Barbier 
2011/3/9 Vlad Arkhipov arhi...@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.

 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] What happens If a table changes during a query/procedure execution

2011-03-09 Thread Nicolas Barbier 
2011/3/9 Nicolas Barbier nicolas.barb...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] What happens If a table changes during a query/procedure execution

2011-03-09 Thread Vlad Arkhipov 

09.03.2011 18:54, Nicolas Barbier:

2011/3/9 Vlad Arkhipovarhi...@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;


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] What happens If a table changes during a query/procedure execution

2011-03-09 Thread Pavel Stehule 
2011/3/9 Vlad Arkhipov arhi...@dc.baikal.ru:
 09.03.2011 18:54, Nicolas Barbier:

 2011/3/9 Vlad Arkhipovarhi...@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



 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers