The following bug has been logged on the website:
Bug reference: 14034
Logged by: Bronislav Houdek
Email address: Bronislav.Houdek@blackboard.com
PostgreSQL version: 9.5.1
Operating system: windows 7 64bit
Description:
We faced with this issue since 9.5 beta version and it is still reproducible
in PostgreSQL 9.5.1 (result of SELECT version() = "PostgreSQL 9.5.1,
compiled by Visual C++ build 1800, 64-bit").
Steps to reproduce:
1. prepare your tables and data (primary key and foreign key is not
mandatory for reproducing this issue):
CREATE TABLE public.table_a
(
id integer NOT NULL,
value text,
CONSTRAINT primary_key PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.table_a
OWNER TO postgres;
CREATE TABLE public.table_b
(
id integer NOT NULL,
value text,
CONSTRAINT foreign_key FOREIGN KEY (id)
REFERENCES public.table_a (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.table_b
OWNER TO postgres;
INSERT INTO table_a (id, value) VALUES (1, 'tableAValue');
INSERT INTO table_b (id, value) VALUES (1, 'tableBValue');
2. use transaction number 1 and launch this sql statement and do not commit
or rollback it, it is very important:
UPDATE table_a SET value = 'tableAValue' WHERE id = 1
3. use transaction number 2 and launch this sql statement:
SELECT TA.value AS ta_value,
(SELECT TB.value FROM table_b TB where TA.id = TB.id) AS tb_value
FROM table_a TA
WHERE TA.id = 1 FOR UPDATE OF TA;
This transaction will wait until the transaction number 1 will be finished.
4. Commit the transaction number 1.
5. Go to the transaction number 2 and verify output of SQL select statement.
There will be columns and rows: (ta_value,tb_value) and ("tableAValue", "")
- notice that value of column tb_value will be empty even if value in DB is
"tableBValue".
Expect columns and rows should be: (ta_value,tb_value) and ("tableAValue",
"tableBValue").
Note:
If you commit the transaction number 1 before executing transaction number 2
then it will work as it is expected with appropriate output.
Thank you for you response, Bronislav Houdek.