From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bernd Hopp <berndjhopp@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: 'within group'- or percentile_cont-expression seems to have
 ramifications on table ordering
In-reply-to: 
 <CAFa2YRwWtVG6d2291FHBc7+9F_-a9GJLGBF-+_eNw3F51MWZJw@mail.gmail.com>
References: 
 <CAFa2YRwWtVG6d2291FHBc7+9F_-a9GJLGBF-+_eNw3F51MWZJw@mail.gmail.com>
Comments: In-reply-to Bernd Hopp <berndjhopp@gmail.com>
	message dated "Sun, 20 Jun 2021 17:42:09 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1510942.1624210238.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 20 Jun 2021 13:30:38 -0400
Message-ID: <1510943.1624210238@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1510943.1624210238%40sss.pgh.pa.us>
Precedence: bulk

Bernd Hopp <berndjhopp@gmail.com> writes:
> you can see that the rows are not in order of insertion any more, but
> in descending order of value. That may not violate the specification,
> however I find it
> to be counterintuitive that a non-updating query would have such side ef=
fects.

On what grounds do you say that UPDATE is a non-updating query?

UPDATE will always place the new row version somewhere else than the
old row version; it cannot simply overwrite the row without violating
ACID semantics.  In the case you show here, the updated versions are
all added at the end of the table, leading to the apparent ordering
change.  This indeed does not violate the specification, because so
far as the SQL spec is concerned, physical row order is simply not
of interest.

			regards, tom lane