From: | b(dot)yordanov2(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14020: row_number() over(partition by order by) - weird behavior |
Date: | 2016-03-14 20:43:48 |
Message-ID: | 20160314204348.8902.26168@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14020
Logged by: Boyko
Email address: b(dot)yordanov2(at)gmail(dot)com
PostgreSQL version: 9.5.0
Operating system: CentOS 6.4
Description:
Hi,
db=# \d+ offers_past_data;
Table "public.offers_past_data"
Column | Type | Modifiers | Storage | Stats target | Description
----------+---------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
feed | integer | not null | plain | |
position | integer | not null | plain | |
db=# \d+ offers_testing;
Table
"public.offers_testing"
Column | Type |
Modifiers | Storage | Stats target | Description
--------------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
id | bigint | not null default
nextval('offers_id_seq'::regclass) | plain | |
grossprice | numeric(11,2) |
| main | |
feed | integer | not null
| plain | |
product | integer |
| plain | |
db=# update offers_past_data a set position = b.position from (select id,
feed, row_number() over(partition by product order by grossprice asc) as
position from offers_testing) b where a.id = b.id and a.feed = b.feed and
a.position <> b.position;
UPDATE 0
This should update every row in offers_past_data when its “position”
changes. In the example above no changes were introduced since the last run
so nothing is updated (expected).
db=# select count(*) from offers_testing where product = 2;
count
-------
99
(1 row)
So there are 99 offers for product 2.
Getting a single offer:
db=# select id,grossprice from offers_testing where product = 2 limit 1;
id | grossprice
---------+------------
4127918 | 5000.00
(1 row)
Updating its grossprice:
db=# update offers_testing set grossprice = 20 where id = 4127918;
UPDATE 1
Now when executing the first query again I expect that no more than 99 rows
get updated in offers_past_data since this is the maximum amount of
positions that would be affected by offer 4127918 grossprice change.
db=# update offers_past_data a set position = b.position from (select id,
feed, row_number() over(partition by product order by grossprice asc) as
position from offers_testing) b where a.id = b.id and a.feed = b.feed and
a.position <> b.position;
UPDATE 104
104 rows get updated.
Executing the same query again a few minutes later (no changes meanwhile in
either table):
db=# update offers_past_data a set position = b.position from (select id,
feed, row_number() over(partition by product order by grossprice asc) as
position from offers_testing) b where a.id = b.id and a.feed = b.feed and
a.position <> b.position;
UPDATE 28058
This time it updates 28058 rows.
This is a test environment and nothing reads or writes to these tables.
Is this a bug or am I missing something obvious?
Regards,
Boyko
From | Date | Subject | |
---|---|---|---|
Next Message | aelmalinka | 2016-03-14 22:52:20 | BUG #14021: configure failing to find shared libperl |
Previous Message | David Steele | 2016-03-14 17:46:40 | Re: BUG #13685: Archiving while idle every archive_timeout with wal_level hot_standby |