BUG #14020: row_number() over(partition by order by) - weird behavior

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

Responses

Browse pgsql-bugs by date

  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