Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qCTOi-0002nG-Ii for pgsql-sql@arkaria.postgresql.org; Thu, 22 Jun 2023 23:07:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qCTOh-0007jq-IF for pgsql-sql@arkaria.postgresql.org; Thu, 22 Jun 2023 23:07:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qCTOh-0007jh-5m for pgsql-sql@lists.postgresql.org; Thu, 22 Jun 2023 23:07:27 +0000 Received: from web.profivps.hu ([2a02:730:1220::87]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qCTOe-0046PH-74 for pgsql-sql@lists.postgresql.org; Thu, 22 Jun 2023 23:07:26 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by web.profivps.hu (Postfix) with ESMTP id AF20552CA775; Fri, 23 Jun 2023 01:07:22 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=profivps.hu; h= content-type:content-type:organization:message-id:user-agent :references:in-reply-to:subject:subject:from:from:date:date :mime-version; s=default; t=1687475241; x=1689289642; bh=+v7/0mR l0ibSoVy7z63of9Mq1znpUE6xT0ja+NybzeM=; b=oOrx5RUYRzBYuXB2YgmRAFH PIxtizPm8hBXVXJPDg1nmGpNx/nFcLJeTz86vhL5vBARq2+PflufFjWLt+Ji/xhG v3LrU3rHBxgVpVSQh2tiJeQZ0d59E76uvMhrp9oxaQIY1E6RSIghTzAbc+KBB0Qe exCJx7BxyE3B3K2mVNFiQB1KU0VlXt5XuIFECpnUxT5sjMkkkYlVMuoLozOxP6Ht yBrH0nJ4UtVlK8uIEqOuAmHVtl0VC3CeVbcbQ3CHWmZr4+jprxJPJlnV9k/1zkGn o5y07ZyeEO2KgEigmeh+LIsbfVPoSet1RCITYyg30E7eoO+/J5BcmfBleio1JlQ= = X-Virus-Scanned: amavisd-new at web.profivps.hu Received: from web.profivps.hu ([127.0.0.1]) by localhost (web.profivps.hu [127.0.0.1]) (amavisd-new, port 10026) with LMTP id dY9mwTRaT3o0; Fri, 23 Jun 2023 01:07:21 +0200 (CEST) Received: from profivps.hu (localhost [IPv6:::1]) by web.profivps.hu (Postfix) with ESMTP id 62C1752CA772; Fri, 23 Jun 2023 01:07:21 +0200 (CEST) MIME-Version: 1.0 Date: Fri, 23 Jun 2023 01:07:21 +0200 From: ProfiVPS Support To: Tom Lane Cc: pgsql-sql@lists.postgresql.org Subject: Re: Strange update behaviour In-Reply-To: <4115432.1687471882@sss.pgh.pa.us> References: <257ece670da80be9e2d08126aba9c641@profivps.hu> <4115432.1687471882@sss.pgh.pa.us> User-Agent: Roundcube Webmail/1.4.11 Message-ID: X-Sender: support@profivps.hu Organization: ProfiVPS.hu Content-Type: multipart/alternative; boundary="=_efe376fd367f38038d7b8d3ee9117934" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_efe376fd367f38038d7b8d3ee9117934 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable 2023-06-23 00:11 id=C5=91pontban Tom Lane ezt =C3=ADrta: > ProfiVPS Support writes: >=20 >> I'm in a dire need of help, because this bug is killing me for weeks >> now. >=20 > 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=20 > case? > (And while you're at it, how about mentioning the PG version?) >=20 > 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=20 upadte of state happens. alertstate is to track if the state change was=20 handled (0: unhandled state change). As Im looking for state change,=20 this update query seemed reasonable, if there is better, I could not=20 think of it :/ I use a function that's called by CollectD to insert values into a=20 table, and part of it is to update the app_devices table with the=20 device's state. This also means data (function calls) arrive in batches,=20 and it is even possible that the same device gets updated multiple times=20 in a batch. It also happens in transactions that are closed every 2=20 seconds. There is only one more task that writes device_alertstate, however,=20 that only selects those records where device_alertstate is 0 (and record=20 is created with 200). Here is what I tested: - added new record to app_devices (device_state=3D0;=20 device_alertstate=3D200; updatets/changets =3D null) - updated via the function with state =3D1 - the result: device_state=3D= 1;=20 device_alertstate=3D200; updatets/changets =3D NOW() - kept updating with state=3D1, nothing changed (except the updatets=20 ofc). - updated via the function with state =3D0 - the result: device_state=3D= 0;=20 device_alertstate=3D0; updatets/changets =3D NOW() - everything works fine from here Then: - new record - update via function with state 1 - the result: device_state=3D1;=20 device_alertstate=3D200; updatets/changets =3D NOW() - update manually to state=3D0 - (device_state=3D0; device_alertstate=3D= 200;=20 updatets/changets =3D unchanged) - update via function with state 1 - the result is the wanted:=20 device_state=3D1; device_alertstate=3D0; updatets/changets =3D NOW() Then: - new record - updated with state=3D0 - the result result is good, although not=20 expected**: device_state=3D0; device_alertstate=3D0; updatets/changets =3D= =20 NOW() - everything works fine from here. ** it is not expected as the default value is 0 and I sent in a 0=20 value. For me this just does not make sense. I tried to replicate this manually with a simple table and the same=20 update query structure, but never happened, every single time all the=20 variables got updated as expected. However, the issue I described=20 happens _a_ _lot_ in production, but as far as I know, it happens=20 randomly. The production runs on deb11: psql (PostgreSQL) 13.11 (Debian=20 13.11-1.pgdg100+1) Same happens on deb10 and ubuntu 20.04. I created some dumps, removed foreign key and such stuff:=20 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,=20 'DE:AD:A7:14:69:9210.123.4.12', 'ruckusphp', '', 'pstates_enabled',=20 'state', '{et}', '{0}', '{1}'); The last {1} is the state value. I hope everything is there to enable testing. Thanks again, Andr=C3=A1s --- Olcs=C3=B3 Virtu=C3=A1lis szerver: http://www.ProfiVPS.hu T=C3=A1mogat=C3=A1s: Support@ProfiVPS.hu --=_efe376fd367f38038d7b8d3ee9117934 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

2023-06-23 00:11 id=C5=91pontban Tom Lane ezt í= ;rta:

= ProfiVPS Support <support@profivp= s.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 t= o
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 versio= n?)

          =   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= =2E It also happens in transactions that are closed every 2 seconds. <= /p>

  There is only one more task that writes device_alertstate, howeve= r, 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=3D0; device_alert= state=3D200; updatets/changets =3D null)

  - updated via the function with state =3D1 - the result: device_s= tate=3D1; device_alertstate=3D200; updatets/changets =3D NOW()

  - kept updating with state=3D1, nothing changed (except the updat= ets ofc). 

  - updated via the function with state =3D0 - the result: device_s= tate=3D0; device_alertstate=3D0; updatets/changets =3D NOW()

  - everything works fine from here

 Then: 

  - new record 

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

  - update manually to state=3D0 - (device_state=3D0; device_alerts= tate=3D200; updatets/changets =3D unchanged)

  - update via function with state 1 - the result is the wanted: de= vice_state=3D1; device_alertstate=3D0; updatets/changets =3D NOW()

 Then: 

  - new record

  - updated with state=3D0 - the result result is good, although no= t expected**: device_state=3D0; device_alertstate=3D0; updatets/changets = =3D 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 sa= me update query structure, but never happened, every single time all the va= riables got updated as expected. However, the issue I described happen= s _a_ _lot_ in production, but as far as I know, it happens randomly. =

  The production runs on deb11: psql (PostgreSQL) 13.11 (Debian 13= =2E11-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', 'ruckusp= hp', '', '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.Pr= ofiVPS.hu

Támogatás: Support@ProfiVPS.hu
--=_efe376fd367f38038d7b8d3ee9117934--