Re: Strange update behaviour - Mailing list pgsql-sql

Tom, 

  thank you. 

  Man, I feel stupid now. It's true: with reason :D I completely forgot about those updates and was only concentrating on the end update, because those previous updates should not even be there....

  I've been banging my head for weeks again and again on this problem, never once scrolled up... Even had a colleague to review it

Thank you very much! Im really grateful :)

Bests, 

András

2023-06-23 03:56 időpontban Tom Lane ezt írta:

ProfiVPS Support <support@profivps.hu> writes:

   I hope everything is there to enable testing.

Thanks for sending a test case!  But I think this is a logic bug
in your function.  There are several updates of app_devices in
that function.  Adding some "raise notice" commands to track the
logic flow, I see that the given case results in

# SELECT collectd_insert(CURRENT_TIMESTAMP,
'DE:AD:A7:14:69:9210.123.4.12', 'ruckusphp', '', 'pstates_enabled',
'state', '{et}', '{0}', '{1}');
NOTICE:  first update happening
NOTICE:  fourth update happening
 collectd_insert
-----------------
 
(1 row)

So it is first doing

                UPDATE app_devices SET device_state=1, device_changets=NOW(), device_updatets=NOW()
                       WHERE device_id= dev_id;

and then later doing the UPDATE you showed.  But at that point,
device_state is already 1 so neither of the device_alertstate or
device_changets updates change the column's value.  It's unobvious
that the device_changets update is a no-op because the first
UPDATE already set it to the same new value.

            regards, tom lane