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

On Thu, Oct 6, 2022 at 12:28 AM 李可强 <52194501011(at)stu(dot)ecnu(dot)edu(dot)cn> wrote:
> 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?

I'm not 100% sure that I understand this question, but I think that
the answer is yes.

> 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.

I believe that your argument is correct (it is wrong) according to some
interpretations of "repeatable read". What Postgres calls repeatable
read is actually snapshot isolation.

The simple fact is that the terminology in this area is confusing, and
it seems as if nobody agrees on all the details. It is generally
understood that the behavior that Postgres calls "repeatable read" is
actually snapshot isolation (same is true of Oracle). So the behavior
that you find surprising is not surprising to us, and is not
considered a bug. I am sympathetic, but it's not going to change now.

This point is made clear in the documentation:

https://www.postgresql.org/docs/current/transaction-iso.html#XACT-REPEATABLE-READ

"A Critique of ANSI SQL Isolation Levels" by Berenson et al explains
this controversy in great detail.

--
Peter Geoghegan