Обсуждение: BUG #14034: Select for update with inner select doesn't return value after committing by other transaction.
BUG #14034: Select for update with inner select doesn't return value after committing by other transaction.
От
Bronislav.Houdek@blackboard.com
Дата:
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.
Bronislav.Houdek@blackboard.com writes: > 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"). I've pushed a fix for this. Thanks for the report! regards, tom lane