On Tue, 20 Feb 2024 at 14:49, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> Also, if the concurrent update were an update of a key
> column that was included in the join condition, the re-scan would
> follow the update to a new matching source row, which is inconsistent
> with what would happen if it were a join to a regular relation.
>
In case it wasn't clear what I was talking about there, here's a simple example:
-- Setup
DROP TABLE IF EXISTS src1, src2, tgt;
CREATE TABLE src1 (a int, b text);
CREATE TABLE src2 (a int, b text);
CREATE TABLE tgt (a int, b text);
INSERT INTO src1 SELECT x, 'Src1 '||x FROM generate_series(1, 3) g(x);
INSERT INTO src2 SELECT x, 'Src2 '||x FROM generate_series(4, 6) g(x);
INSERT INTO tgt SELECT x, 'Tgt '||x FROM generate_series(1, 6, 2) g(x);
-- Session 1
BEGIN;
UPDATE tgt SET a = 2 WHERE a = 1;
-- Session 2
UPDATE tgt t SET b = s.b
FROM (SELECT * FROM src1 UNION ALL SELECT * FROM src2) s
WHERE s.a = t.a;
SELECT * FROM tgt;
-- Session 1
COMMIT;
and the result in tgt is:
a | b
---+--------
2 | Src1 2
3 | Src1 3
5 | Src2 5
(3 rows)
whereas if that UNION ALL subquery had been a regular table with the
same contents, the result would have been:
a | b
---+--------
2 | Tgt 1
3 | Src1 3
5 | Src2 5
i.e., the concurrently modified row would not have been updated.
Regards,
Dean