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
|
Список | 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 по дате отправления: