Re:Re: Fwd: BUG #17017: Two versions of the same row of records are returned in one query

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(at)stu(dot)ecnu(dot)edu(dot)cn"<52194501011(at)stu(dot)ecnu(dot)edu(dot)cn>;
Cc: "tgl"; "pgsql-bugs";
Subject: Re: Fwd: BUG #17017: Two versions of the same row of records are returned in one query

&nbsp;
On Wed, May 19, 2021 at 12:27 AM 李可强 <52194501011(at)stu(dot)ecnu(dot)edu(dot)cn&gt; wrote:
&gt; 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