The following bug has been logged on the website:
Bug reference: 14020
Logged by: Boyko
Email address: b.yordanov2@gmail.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