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;