BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT
Дата
Msg-id 15906-d64845e69b79f9dd@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      15906
Logged by:          Hugo Mildenberger
Email address:      px812@mailbox.org
PostgreSQL version: 11.4
Operating system:   Gentoo Linux
Description:

--  13.2.1. Read Committed Isolation Level
--   
--  [...] However, SELECT does see the effects of previous updates executed
within 
--        its own transaction, even though they are not yet committed.
[...]
--  Source: https://www.postgresql.org/docs/11/transaction-iso.html

--  Assuming the term "previous updates" as cited above also includes insert
operations, the 
--  following example shows that SELECT actually does NOT see uncommitted
data within 
--  its own transaction.

CREATE TABLE xtmp( name TEXT);

CREATE OR REPLACE FUNCTION itest1( aName Text) RETURNS SETOF xtmp AS $$
BEGIN
   RETURN QUERY
     WITH ix AS ( 
          INSERT INTO xtmp(name) VALUES(aName) RETURNING *
     ) SELECT * FROM xtmp WHERE name = (SELECT name from ix);
--    Same result as with 
--     ) SELECT * FROM xtmp;  
--     ) SELECT * FROM xtmp WHERE name = aName;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION itest2( aName Text) RETURNS SETOF xtmp AS $$
BEGIN
   INSERT INTO xtmp(name) VALUES(aName);
   RETURN QUERY  SELECT * FROM xtmp WHERE name = aName;
END
$$ LANGUAGE plpgsql;

--  Test log:
--
--  opk=# begin;
--  opk=# select * from itest1('hello');
--  name
--  ------
--  (0 rows)
--
--  opk=# select * from itest1('hello');
--  name
--  -------
--  hello
--  (1 row)
-- 
--  opk=# select * from xtmp;
--  name
--  -------
--  hello
--  hello
--  (2 rows)
--
--  opk=# commit;
--
--  opk=# delete from xtmp;
--  DELETE 2
--
--  opk=# begin;
--  opk=# select * from itest2('hello');
--  name
--  -------
--  hello
--  (1 row)
--
--  opk=# select * from itest2('hello');
--  name
--  -------
--  hello
--  hello
--  (2 rows)
--
--  opk=# select * from xtmp;
--  name
--  -------
--  hello
--  hello
--  (2 rows)
--
--  opk=# commit;


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: BUG #15905: FATAL: the database system is starting up
Следующее
От: Andrew Gierth
Дата:
Сообщение: Re: BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT