pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed'within group'- or percentile_cont-expression seems to have ramifications on table ordering
3+ messages / 2 participants
[nested] [flat]
* 'within group'- or percentile_cont-expression seems to have ramifications on table ordering
@ 2021-06-20 15:42 Bernd Hopp <berndjhopp@gmail.com>
2021-06-20 17:30 ` Re: 'within group'- or percentile_cont-expression seems to have ramifications on table ordering Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Bernd Hopp @ 2021-06-20 15:42 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
here's how to reproduce. create a test table:
create table test (
id integer not null primary key,
value integer not null,
value_percentile integer not null default 0
);
insert random values into the 'value' column
do $$
begin
for r in 0..100 loop
insert into test(id, value) values (r, random() * 100);
end loop;
end;
$$;
compute the percentiles for those values and write them to the percentile-column
do $$
declare
_value integer;
begin
for r in 0..100 loop
select into _value percentile_cont(r::float / 100)
within group (order by test.value) from test;
raise notice '%: %',r::float / 100, _value;
update test set value_percentile = r where value = _value;
end loop;
end
$$;
inspect the table
select * from test;
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 effects.
[image: image.png]
best
Bernd
Attachments:
image.png (image/png, 32.8K) download
view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 'within group'- or percentile_cont-expression seems to have ramifications on table ordering
2021-06-20 15:42 'within group'- or percentile_cont-expression seems to have ramifications on table ordering Bernd Hopp <berndjhopp@gmail.com>
@ 2021-06-20 17:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-20 17:45 ` Re: 'within group'- or percentile_cont-expression seems to have ramifications on table ordering Bernd Hopp <berndjhopp@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-06-20 17:30 UTC (permalink / raw)
To: Bernd Hopp <berndjhopp@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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 effects.
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 'within group'- or percentile_cont-expression seems to have ramifications on table ordering
2021-06-20 15:42 'within group'- or percentile_cont-expression seems to have ramifications on table ordering Bernd Hopp <berndjhopp@gmail.com>
2021-06-20 17:30 ` Re: 'within group'- or percentile_cont-expression seems to have ramifications on table ordering Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-06-20 17:45 ` Bernd Hopp <berndjhopp@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Bernd Hopp @ 2021-06-20 17:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Thank you for the clarification
Tom Lane <tgl@sss.pgh.pa.us> schrieb am So., 20. Juni 2021, 19:30:
> 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
> effects.
>
> 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-06-20 17:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-20 15:42 'within group'- or percentile_cont-expression seems to have ramifications on table ordering Bernd Hopp <berndjhopp@gmail.com>
2021-06-20 17:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-20 17:45 ` Bernd Hopp <berndjhopp@gmail.com>
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