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 1qCaiW-0006o3-Ry for pgsql-sql@arkaria.postgresql.org; Fri, 23 Jun 2023 06:56:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qCaiV-0003AS-8f for pgsql-sql@arkaria.postgresql.org; Fri, 23 Jun 2023 06:56:23 +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 1qCaiU-0003AJ-Tt for pgsql-sql@lists.postgresql.org; Fri, 23 Jun 2023 06:56:22 +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 1qCaiS-004A6Z-1x for pgsql-sql@lists.postgresql.org; Fri, 23 Jun 2023 06:56:22 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by web.profivps.hu (Postfix) with ESMTP id 81C8652C1DD8; Fri, 23 Jun 2023 08:56:18 +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=1687503377; x=1689317778; bh=dfmk8j7 LTdo7KLpveED08WPTGVJHQE8e+erE0LgPecI=; b=BGYXVllsZwd689SOPzXT/z4 gNLl9L/51N4ankU0nk6TPhwF3e7jWMaOQbLuQqPGQMhJplN/eZBWC58KaGqbLDAL CeX1BJR75xmih3Dd9ZdtsNhTe3qHi+hSgdRBpODrPdx1iIHQagsJjbQS1EvDZOLp ZakuTtEBrSle6HcBj2Yf0Lg1kcENff5cf7mblVkigl1WN2+jTeTENLs/BzWmbILX DXmU6lRKGVJ0ZBSZcQa+Sb8ltLffOgSC6WqML+cW8UCtSKK62DtBqX5zr/FIW9pi 2p4A0TntIC2SyJTpbarwDjAF/0QErlrtV0KQb2oRq48rnfvST8SW9C2mI7UQsKA= = 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 XA25RmEdt3zx; Fri, 23 Jun 2023 08:56:17 +0200 (CEST) Received: from profivps.hu (localhost [IPv6:::1]) by web.profivps.hu (Postfix) with ESMTP id 6BB4152C1DD7; Fri, 23 Jun 2023 08:56:17 +0200 (CEST) MIME-Version: 1.0 Date: Fri, 23 Jun 2023 08:56:17 +0200 From: ProfiVPS Support To: Tom Lane Cc: pgsql-sql@lists.postgresql.org Subject: Re: Strange update behaviour In-Reply-To: <4141157.1687485367@sss.pgh.pa.us> References: <257ece670da80be9e2d08126aba9c641@profivps.hu> <4115432.1687471882@sss.pgh.pa.us> <4141157.1687485367@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="=_aa5f6bb717b98c8e8fe0ecfa954d8250" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_aa5f6bb717b98c8e8fe0ecfa954d8250 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable Tom, thank you. Man, I feel stupid now. It's true: with reason :D I completely forgot=20 about those updates and was only concentrating on the end update,=20 because those previous updates should not even be there.... I've been banging my head for weeks again and again on this problem,=20 never once scrolled up... Even had a colleague to review it Thank you very much! Im really grateful :) Bests, Andr=C3=A1s 2023-06-23 03:56 id=C5=91pontban Tom Lane ezt =C3=ADrta: > ProfiVPS Support writes: >=20 >> I hope everything is there to enable testing. >=20 > 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 >=20 > # 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 > ----------------- >=20 > (1 row) >=20 > So it is first doing >=20 > UPDATE app_devices SET device_state=3D1, device_changets=3DNOW(),=20 > device_updatets=3DNOW() > WHERE device_id=3D dev_id; >=20 > 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. >=20 > regards, tom lane --- Olcs=C3=B3 Virtu=C3=A1lis szerver: http://www.ProfiVPS.hu T=C3=A1mogat=C3=A1s: Support@ProfiVPS.hu --=_aa5f6bb717b98c8e8fe0ecfa954d8250 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

Tom, 

  thank you. 

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

  I've been banging my head for weeks again and again on this probl= em, 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=C5=91pontban Tom Lane ezt í= ;rta:

= ProfiVPS Support <support@profivp= s.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 inthat function.  Adding some "raise notice" commands to track thelogic flow, I see that the given case results in

# SELECT co= llectd_insert(CURRENT_TIMESTAMP,
'DE:AD:A7:14:69:9210.123.4.12', 'ruc= kusphp', '', 'pstates_enabled',
'state', '{et}', '{0}', '{1}');
= NOTICE:  first update happening
NOTICE:  fourth update happe= ning
 collectd_insert
-----------------
 
(1= row)

So it is first doing

    &n= bsp;           UPDAT= E app_devices SET device_state=3D1, device_changets=3DNOW(), device_updatet= s=3DNOW()
          = ;            &n= bsp;WHERE device_id=3D dev_id;

and then later doing the UPDATE y= ou showed.  But at that point,
device_state is already 1 so neith= er of the device_alertstate or
device_changets updates change the colu= mn'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



= ---
Olcsó Virtuális szerver:
http://www.Pr= ofiVPS.hu

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