How did this statement update 3 rows in Postgres?

Question

I am curious how this statement updated 3 rows in Postgres. All the other times I ran it, it would update 0 or 1. Is there a way to find out which rows?

bestsales=# update keyword set revenue = random()*10 where id = cast(random()*99999 as int);
UPDATE 3

id is the primary key.

 id               | integer                        | not null default nextval('keyword_id_seq'::regclass)
    "keyword_pkey" PRIMARY KEY, btree (id)

I tried to run it as SELECT:

bestsales=# select * from keyword where id = cast(random()*99999 as int);
  id   |       keyword       | seed_id | source | search_count | country | language | volume | cpc  | competition | modified_on | google_violation | revenue | bing_violation
-------+---------------------+---------+--------+--------------+---------+----------+--------+------+-------------+-------------+------------------+---------+----------------
  6833 | vizio m190mv        |         | GOOGLE |            0 |         |          |     70 | 0.38 |        0.90 |             |                  |         |
 65765 | shiatsu massage mat |         | SPYFU  |            0 |         |          |    110 | 0.69 |             |             |                  |         |
 87998 | granary flour       |         | SPYFU  |            0 |         |          |     40 | 0.04 |             |             |                  |         |
(3 rows)

And sometimes it would return more than one. How is that possible?

PostgreSQL 9.5.3

Answer 1

Even a simple subquery does the job:

UPDATE keyword
SET    revenue = random() * 10
WHERE  id = (SELECT (random() * 99999)::int);

The subquery is evaluated once and only one row is updated (if the id value exists). A bit cheaper than using a CTE.

If you use the expression without subquery wrapper, random() is evaluated for every row, because it is a volatile function. That's by design and often the desired behavior.

As for your additional question:

Is there a way to find out which rows?

Not with guaranteed safety, but if you execute this query immediately after the UPDATE, the rows sharing the highest transaction ID in xmin value are the ones most recently updated or inserted.

All rows inserted / updated in the same transaction share the same transaction ID. If you have multiple INSERT / UPDATE operations to the same table in one transaction you need to look at the command ID cmin additionally:

SELECT *, xmin, cmin
FROM   keyword
ORDER  BY xmin::text::bigint DESC;

There are no guarantees because xmin is subject to possible transaction ID wraparound. But it normally works.

xmin and cmin are system columns in the row header.
The manual about object identifier types.

While still in the same transaction (update not committed yet), there is an even safer method:

• How to view tuples changed in a PostgreSQL transaction?

• How to compare xmin and txid_current() after transactions ID wraparound?

Related:

• Can I select data inserted in the same uncommited transaction?

Answer 2

It seems that random() is executed once per row and not once for all the statement. So each id value is checked against a different random value. You may get 0, 1, 2 or more, even all the rows of the table, although that possibility would be very small even with 10 rows.

If you want it to be executed once and thus get only one value and update only zero or one rows, you could use a CTE, which are known to be evaluated before the main query:

with rnd as
  ( select random() as random )               -- get just one value
select * 
from keyword, rnd 
where id = cast(rnd.random * 99999 as int) ;  -- and use it everywhere

and the same for the UPDATE:

with rnd as
  ( select random() as random ) 
update keyword
set revenue = random()*10                    -- different random value
from rnd 
where id = cast(rnd.random * 99999 as int);