public inbox for [email protected]  

help / color / mirror / Atom feed

From: ProfiVPS Support <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Strange update behaviour
Date: Fri, 23 Jun 2023 01:07:21 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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

> ProfiVPS Support <[email protected]> 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

---
Olcsó Virtuális szerver:
http://www.ProfiVPS.hu

Támogatás: [email protected]

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Strange update behaviour
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox