Hi everyone,
Recently I got unexpected results from a query that seems to be legit.
The setup is like this:
-- setup
CREATE TABLE parents
(
id uuid not null primary key,
revision integer not null
);
CREATE TABLE children
(
id uuid not null primary key,
parent_id uuid not null references parents
);
INSERT INTO parents (id, revision)
VALUES ('ec422e09-55bb-4465-a990-31f59859959d', 1);
INSERT INTO children (id, parent_id)
VALUES ('5cb82ceb-c5ef-4c59-a02e-f7b610470f8c', 'ec422e09-55bb-4465-a990-31f59859959d');
INSERT INTO children (id, parent_id)
VALUES ('ce5b22b0-c6c4-4c09-826c-7086c53ee9ec', 'ec422e09-55bb-4465-a990-31f59859959d');
The query is:
-- query
SELECT children_union.id AS child_id
FROM parents p
JOIN (SELECT id, parent_id
FROM children
UNION ALL
SELECT null::uuid, null::uuid
WHERE false) children_union ON children_union.parent_id = p.id
WHERE p.id = 'ec422e09-55bb-4465-a990-31f59859959d'
FOR UPDATE OF p;
It looks weird, but it's just a simplification of a much bigger query. The 'SELECT null::uuid, null::uuid WHERE false' part was actually more meaningful but I substituted it with a query that returns 0 rows after finding out that it's irrelevant.
If I just run this query I get something that I would expect to get:
-- result 1
child_id
--------------------------------------
5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
ce5b22b0-c6c4-4c09-826c-7086c53ee9ec
(2 rows)
But if I lock the single row in the parents table:
-- concurrent query
BEGIN;
UPDATE parents
SET revision = revision + 1
WHERE id = 'ec422e09-55bb-4465-a990-31f59859959d';
and then run my query again in a separate session, then it's waiting for the lock to be released. Once I commit the concurrent query and release the lock, I get this:
-- result 2
child_id
--------------------------------------
5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
(2 rows)
but I would expect to get the same result as previously. Is it a bug or am I doing something wrong here?
Thank you,
Andrii
