From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
---|---|
To: | zwj <sxzwj(at)vip(dot)qq(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445) |
Date: | 2024-02-20 15:10:11 |
Message-ID: | CAEZATCVCMY1Z_xiNua9c3Eb=vF5QWPR50g=o+bfE-WBs6AWpUw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, 20 Feb 2024 at 14:49, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-02-20 15:16:00 | Re: numeric_big in make check? |
Previous Message | Daniel Gustafsson | 2024-02-20 14:52:36 | Re: Replace current implementations in crypt() and gen_salt() to OpenSSL |