Re: Strange update behaviour - Mailing list pgsql-sql

2023-06-23 00:11 időpontban Tom Lane ezt írta:

ProfiVPS Support <support@profivps.hu> writes:

   I'm in a dire need of help, because this bug is killing me for weeks
now.

What you showed us seems odd, but with zero context it's impossible to
tell what's really going on.  Can you provide a self-contained test case?
(And while you're at it, how about mentioning the PG version?)

            regards, tom lane

Hi there, 

  thank you for your reply. 

  I do agree it's odd. 

  The changets is when the new and old state differs. UpdateTs when an upadte of state happens. alertstate is to track if the state change was handled (0: unhandled state change). As Im looking for state change, this update query seemed reasonable, if there is better, I could not think of it :/

  I use a function that's called by CollectD to insert values into a table, and part of it is to update the app_devices table with the device's state. This also means data (function calls) arrive in batches, and it is even possible that the same device gets updated multiple times in a batch. It also happens in transactions that are closed every 2 seconds. 

  There is only one more task that writes device_alertstate, however, that only selects those records where device_alertstate is 0 (and record is created with 200).

  Here is what I tested: 

  - added new record to app_devices (device_state=0; device_alertstate=200; updatets/changets = null)

  - updated via the function with state =1 - the result: device_state=1; device_alertstate=200; updatets/changets = NOW()

  - kept updating with state=1, nothing changed (except the updatets ofc). 

  - updated via the function with state =0 - the result: device_state=0; device_alertstate=0; updatets/changets = NOW()

  - everything works fine from here

 Then: 

  - new record 

  - update via function with state 1 - the result: device_state=1; device_alertstate=200; updatets/changets = NOW()

  - update manually to state=0 - (device_state=0; device_alertstate=200; updatets/changets = unchanged)

  - update via function with state 1 - the result is the wanted: device_state=1; device_alertstate=0; updatets/changets = NOW()

 Then: 

  - new record

  - updated with state=0 - the result result is good, although not expected**: device_state=0; device_alertstate=0; updatets/changets = NOW()

  - everything works fine from here. 

 ** it is not expected as the default value is 0 and I sent in a 0 value. 

  For me this just does not make sense. 

  I tried to replicate this manually with a simple table and the same update query structure, but never happened, every single time all the variables got updated as expected. However, the issue I described happens _a_ _lot_ in production, but as far as I know, it happens randomly. 

  The production runs on deb11: psql (PostgreSQL) 13.11 (Debian 13.11-1.pgdg100+1)

  Same happens on deb10 and ubuntu 20.04.

  I created some dumps, removed foreign key and such stuff: https://pastebin.com/rju4eD9n

  And here is the function: https://pastebin.com/vM2MvGug  

  Calling the function like this should work: 

  SELECT collectd_insert(CURRENT_TIMESTAMP, 'DE:AD:A7:14:69:9210.123.4.12', 'ruckusphp', '', 'pstates_enabled', 'state', '{et}', '{0}', '{1}');

  The last {1} is the state value. 

  I hope everything is there to enable testing. 

Thanks again,

András