Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tZ7a5-001PF4-42 for pgsql-general@arkaria.postgresql.org; Sat, 18 Jan 2025 12:05:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tZ7a3-00HYKr-9O for pgsql-general@arkaria.postgresql.org; Sat, 18 Jan 2025 12:05:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tZ7a2-00HYKi-Tt for pgsql-general@lists.postgresql.org; Sat, 18 Jan 2025 12:05:35 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tZ7a0-000Blq-1d for pgsql-general@postgresql.org; Sat, 18 Jan 2025 12:05:35 +0000 Received: by mail-ej1-x632.google.com with SMTP id a640c23a62f3a-ab39f84cbf1so171950866b.3 for ; Sat, 18 Jan 2025 04:05:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1737201932; x=1737806732; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=+PjtbgIp1k/x1vgPEQnjkYB+/1UQnRxA25DWhB4QFqo=; b=XC2XK4ATYWuiAohR5ic+jzV5F6H8e5G8Q7mQ55ucipsxcRDZ96T9nQ3UH1wkvmhutp n/BVPCdWhCTvkt7J6VXzOpqh+h31cwU5g13HBjIHik4b7bTC2CNBuysJa57LVQrZsU83 ZP8rMHUDoC0pSBW1R0vy63MLqxzuE/Dg/lTNaUuCseyTOeoCmWJi29OOE24badGNVnIw n+Fu15sxqgUgsFYgSjrP4/2+MyUHLTrGdlPMbGQSXWJLQh4QyzfC4o8jek4p/MV/k2Fc Bx4BiW6BEcoKsXwXyN43TF1qpLSWR6y8qHxe/sv3CGQFquh8uPVRH7CbF6Q2GR5hCjuO MNVQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737201932; x=1737806732; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=+PjtbgIp1k/x1vgPEQnjkYB+/1UQnRxA25DWhB4QFqo=; b=X8wbwHH8pDPUpWzRVKFBY8llwJ7VQjQqK+W+wiOgMsF9HZgXEfCI+CITszUDxe2+LP +HL8GQKxfID8wtyolmOL7v/7wHEz/+1gvd5TQOdzTMps5uq69yIlHJK6ZjwCUlx3zzbI nMNFD46/PnCmXK3k7lZ5U5feh99CgPzio3vabZvkn4cCpHwluD5aii3xA52QgsKSdEe7 F7jh8dyel1M6NE/ZhZ93WvZ3RwisPs1m6AXy0/yTH2DiIoG8mtJjo02ZcpbEq14BBJ2x qKSWkIPwsrglKpduWEH+CIldTpT4XnkqpqfDZxNxahGjEzcJ8P8LtdrdkSX5XFS9+GtW JZnA== X-Gm-Message-State: AOJu0Ywiq79JOlpdSAzIcD67tf8GA1GebX4TvPygt5QlHiIpSOpPFKbG rYU+QvLVpe8cb2rB25mRh0N2H3beiIbWuPOja7UG2T8pT6oXtsNVNr0qAxQFJ4HKyW7BkwQzkY4 95EkK2UQidsp37eB7xZ3cjvi7Jb0= X-Gm-Gg: ASbGncucc+gGhpDUhazk9Dj5/ueI5cmZH50F18LDNYgq3WKjwCGNiphZuLOCgLk8URl Nv3SiIjZqiZSWh7twcfZcH5La87fsJ8VAJdwjRFvUd1oV7VGN4xDS X-Google-Smtp-Source: AGHT+IE1dWOyWdcoskdkh7JUd0B/SdS+bgTAXwpUudrw0Pq8+H8c/w95V0uoTK8FSoZk9vyLJuvbudJBwVnmQkdB/j0= X-Received: by 2002:a17:906:1788:b0:ab3:47cc:a7dd with SMTP id a640c23a62f3a-ab38b15d31dmr486003466b.25.1737201931593; Sat, 18 Jan 2025 04:05:31 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: GF Date: Sat, 18 Jan 2025 13:05:20 +0100 X-Gm-Features: AbW1kvZGl0yOdZeac9CxU-JlQWmxPrABtnHeHwuFeMpQjqsqZGFurK1_e0s2liA Message-ID: Subject: Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function? To: Alexander Farber Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000000931dc062bf9d8b9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000931dc062bf9d8b9 Content-Type: text/plain; charset="UTF-8" Hi Alex, On Sat, 18 Jan 2025 at 12:21, Alexander Farber wrote: > [...] > > -- Run 2 smoke tests > DO $$ > DECLARE > test_result RECORD; > BEGIN > -- Test 1: store 15x4 records expiring in 5 seconds > SELECT * INTO test_result FROM test_store_vehicle_data(15); > IF test_result.count_true != 10 OR test_result.count_false != 5 THEN > RAISE EXCEPTION 'Test 1 failed: expected 10 TRUE, 5 FALSE'; > END IF; > > -- Sleep for 10 seconds, so that all records in vehicle_data expire > PERFORM pg_sleep(10); > > -- Test 2: store 15x4 records expiring in 5 seconds > SELECT * INTO test_result FROM test_store_vehicle_data(15); > IF test_result.count_true != 10 OR test_result.count_false != 5 THEN > RAISE EXCEPTION 'Test 2 failed: expected 10 TRUE, 5 FALSE'; > END IF; > END $$; > > -- Print all records in the vehicle_data table > SELECT expires_at < NOW() AS expired, * FROM vehicle_data ORDER BY > container_id; > > My problem is that the DELETE FROM vehicle_data WHERE NOW() > expires_at; > statement in my store_vehicle_data() function does not see to delete > anything. > > And then the SELECT expires_at < NOW() AS expired, * FROM vehicle_data > ORDER BY container_id; prints the records in the table and yes, they are > all expired there. > > I run the above SQL code (creating tables, creating functions, running > smoke test) in a Dockerfile based on the official Postgres Dockerfile and > the smoke test (the Test 2) just always fails. > > Then I connect using psql to my docker container and run the DELETE > command at the psql prompt and voila - it works there as expected and > deletes all the expired records. > > The function now() returns the timestamp of the transaction start, so it will be stable during all your "do $$ ...end; $$;" smoke test toplevel block: i.e., it will return the same value across the two test_store_vehicle_data() invocations, so the predicate "where NOW() > expires_at" in store_vehicle_data() will return no rows. That's also why running a second time the delete command it worked: you were running it in another transaction after more than 10 seconds from the first one. If you want to keep a stable semantics (i.e., still use now() in your store_vehicle_data() function), then you should split your smoke test in two distinct "do $$ end; $$;" invocations, one for the first call to test_store_vehicle_data() with pg_sleep(), and one for the second call. Otherwise you could use clock_timestamp() instead of now() inside store_vehicle_data(), as this returns the actual current timestamp (maybe storing it in a variable _x to be used in the "where _x > expires_at" condition, to avoid the "sliding predicate" effect of "where clock_timestamp() > expires_at"). Best, giovanni --0000000000000931dc062bf9d8b9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Alex,

On Sat, 18 Jan 2025= at 12:21, Alexander Farber <alexander.farber@gmail.com> wrote:
[...]
<= div dir=3D"ltr">

-- Run 2 smoke tests
DO $= $
DECLARE
=C2=A0 =C2=A0 test_result RECORD;
B= EGIN
=C2=A0 =C2=A0 -- Test 1: store 15x4 records expiring in 5 se= conds
=C2=A0 =C2=A0 SELECT * INTO test_result FROM test_store_veh= icle_data(15);
=C2=A0 =C2=A0 IF test_result.count_true !=3D 10 OR= test_result.count_false !=3D 5 THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = RAISE EXCEPTION 'Test 1 failed: expected 10 TRUE, 5 FALSE';
=C2=A0 =C2=A0 END IF;

=C2=A0 =C2=A0 -- Sleep fo= r 10 seconds, so that all records in vehicle_data expire
=C2=A0 = =C2=A0 PERFORM pg_sleep(10);

=C2=A0 =C2=A0 -- Test= 2: store 15x4 records expiring in 5 seconds
=C2=A0 =C2=A0 SELECT= * INTO test_result FROM test_store_vehicle_data(15);
=C2=A0 =C2= =A0 IF test_result.count_true !=3D 10 OR test_result.count_false !=3D 5 THE= N
=C2=A0 =C2=A0 =C2=A0 =C2=A0 RAISE EXCEPTION 'Test 2 failed:= expected 10 TRUE, 5 FALSE';
=C2=A0 =C2=A0 END IF;
= END $$;

-- Print all records in the vehicle_data t= able
SELECT expires_at < NOW() AS expired, * FROM vehicle_data= ORDER BY container_id;

My problem is that the DEL= ETE FROM vehicle_data WHERE NOW() > expires_at; statement in my store_ve= hicle_data() function does not see to delete anything.

=
And then the SELECT expires_at < NOW() AS expired, * FROM vehicle_d= ata ORDER BY container_id; prints the records in the table and yes, they ar= e all expired there.

I run the above SQL code (cre= ating tables, creating functions, running smoke test) in a Dockerfile based= on the official Postgres Dockerfile and the smoke test (the Test 2) just a= lways fails.

Then I connect using psql to my docke= r container and run the DELETE command at the psql prompt and voila - it wo= rks there as expected and deletes all the expired records.
=

=

The function= now() returns the timestamp of the transaction start, so it will be stable= during all your "do $$ ...end; $$;" smoke test toplevel block: i= .e., it will return the same value across the two test_store_vehicle_data()= invocations, so the predicate "where NOW() > expires_at" in s= tore_vehicle_data() will return no rows. That's also why running a seco= nd time the delete command it worked: you were running it in another transa= ction after more than 10 seconds from the first one.
If you w= ant to keep a stable semantics (i.e., still use now() in your store_vehicle= _data() function), then you should split your smoke test in two distinct &q= uot;do $$ end; $$;" invocations, one for the first call to test_store_= vehicle_data() with pg_sleep(), and one for the second call.
= Otherwise you could use clock_timestamp() instead of now() inside store_veh= icle_data(), as this returns the actual current timestamp (maybe storing it= in a variable _x to be used in the "where _x > expires_at" co= ndition, to avoid the "sliding predicate" effect of "where c= lock_timestamp() > expires_at").

Best,=
giovanni

--0000000000000931dc062bf9d8b9--