From: 李可强 <52194501011@stu.ecnu.edu.cn>
To: Peter Geoghegan <pg@bowt.ie>
Cc: tgl <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re:Re: Fwd: BUG #17017: Two versions of the same row of records are returned in one query
Date: Thu, 6 Oct 2022 15:28:28 +0800
Message-ID: <tencent_1113A409264D6BCD0ADCE312@qq.com> (raw)
In-Reply-To: <CAH2-WznF4rHqCZWkTHi7XAcNxp80W9DkYLuQ9Mp6ZusN9N2rnQ@mail.gmail.com>
References: <17017-c37dbbadb77cfde9@postgresql.org>
<3229567.1621354699@sss.pgh.pa.us>
<CACig7B6=D8NDExtZaBGrU=DpF=6esy+hJEDQ+vmDw6g4b2ponw@mail.gmail.com>
<tencent_53E42BE16C22D6872833143B@qq.com>
<CAH2-WznF4rHqCZWkTHi7XAcNxp80W9DkYLuQ9Mp6ZusN9N2rnQ@mail.gmail.com>
Hi Peter,
Thank you for your kind response. If possible, I want to discuss more about my confusion on this problem.
You have mentioned that "a predicate on the PK is not special in Postgres". Does it mean that PG takes PK as a common column instead of the identifier of rows if selecting rows by a predicate on the PK? If it is so, in a relational database, PG may break the unique constraints on PK.
For example, in my mentioned bug case, the query on PG returns two rows which have the same PK. From my point of view, such a behavior violates the primary key unique constraint.
Look forward to the response.
Thank you.
------------------ Original ------------------
From: "Peter Geoghegan";
Date: 2021年5月20日(星期四) 上午9:56
To: "52194501011@stu.ecnu.edu.cn"<52194501011@stu.ecnu.edu.cn>;
Cc: "tgl"; "pgsql-bugs";
Subject: Re: Fwd: BUG #17017: Two versions of the same row of records are returned in one query
On Wed, May 19, 2021 at 12:27 AM 李可强 <52194501011@stu.ecnu.edu.cn> wrote:
> There is no need to return the old version written by committed transactions. The query result fo Session B is very confusing!
FWIW I think that it's a matter of how the standard is interpreted.
You expect something that Kyle Kingsbury called "Adya repeatable
read". This interpretation of RR allows an application which only
accesses records by primary key to get serializable execution.
Essentially, the primary key is special in a system that uses
traditional two-phase locking (or something closer to it than the
Postgres model). But a predicate on the PK is not special in Postgres.
--
Peter Geoghegan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: 52194501011@stu.ecnu.edu.cn, pg@bowt.ie, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re:Re: Fwd: BUG #17017: Two versions of the same row of records are returned in one query
In-Reply-To: <tencent_1113A409264D6BCD0ADCE312@qq.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox