BUG #14034: Select for update with inner select doesn't return value after committing by other transaction.

Поиск
Список
Период
Сортировка
От Bronislav.Houdek@blackboard.com
Тема BUG #14034: Select for update with inner select doesn't return value after committing by other transaction.
Дата
Msg-id 20160318140937.2905.58185@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #14034: Select for update with inner select doesn't return value after committing by other transaction.  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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.

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

Предыдущее
От: ruslan.zakirov@gmail.com
Дата:
Сообщение: BUG #14032: trigram index is not used for '=' operator
Следующее
От: daniel@makrotopia.org
Дата:
Сообщение: BUG #14033: cross-compilation to ARM fails