From: Tom Lane <tgl@sss.pgh.pa.us>
To: ProfiVPS Support <support@profivps.hu>
cc: pgsql-sql@lists.postgresql.org
Subject: Re: Strange update behaviour
In-reply-to: <b6caf8ca0dc6235c509ad32207dd5be4@profivps.hu>
References: <257ece670da80be9e2d08126aba9c641@profivps.hu>
 <4115432.1687471882@sss.pgh.pa.us>
 <b6caf8ca0dc6235c509ad32207dd5be4@profivps.hu>
Comments: In-reply-to ProfiVPS Support <support@profivps.hu>
	message dated "Fri, 23 Jun 2023 01:07:21 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4141156.1687485367.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 22 Jun 2023 21:56:07 -0400
Message-ID: <4141157.1687485367@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4141157.1687485367%40sss.pgh.pa.us>
Precedence: bulk

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=3D1, device_changets=3D=
NOW(), device_updatets=3DNOW() =

                       WHERE device_id=3D 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