MIME-Version: 1.0
References: <CAADeyWjuNpncDm=pEQfOpLOgc_EQnd6B9sKfxQT8zGKJufcjeQ@mail.gmail.com>
 <CAFePLY0G3UmzdbfdJs5Ngdo4GoQKpSW2okfp00hvpm-HBkGYRQ@mail.gmail.com>
In-Reply-To: <CAFePLY0G3UmzdbfdJs5Ngdo4GoQKpSW2okfp00hvpm-HBkGYRQ@mail.gmail.com>
From: Alexander Farber <alexander.farber@gmail.com>
Date: Sat, 18 Jan 2025 15:04:07 +0100
Message-ID: <CAADeyWgSH+0amMokkijRXyOfFW88-QRw8CzOfjZNW89MCJZNMw@mail.gmail.com>
Subject: Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing
 in my stored function?
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d0ea59062bfb800a"
Archived-At: <https://www.postgresql.org/message-id/CAADeyWgSH%2B0amMokkijRXyOfFW88-QRw8CzOfjZNW89MCJZNMw%40mail.gmail.com>
Precedence: bulk

--000000000000d0ea59062bfb800a
Content-Type: text/plain; charset="UTF-8"

Thank you Giovanni, I did not expect this from NOW() and that is why I was
stuck.

I have decided to keep NOW() in my stored function in the hope it has
better performance.

To fix the issue I have rewritten my smoke tests to be plain SQL without
any transaction:

CREATE OR REPLACE FUNCTION test_store_vehicle_data(
    num_runs INTEGER,
    OUT count_true INTEGER,
    OUT count_false INTEGER
)
RETURNS RECORD AS $$
DECLARE
    test_result BOOLEAN;
BEGIN
    count_true := 0;
    count_false := 0;

    FOR i IN 1..num_runs LOOP
        -- Store OSM node ids (2 are same, 2 are changing)
        -- with node limit 10 and retention time 5 seconds
        test_result := store_vehicle_data(
            100 + i,
            ARRAY[1000, 2000, 3000 + i, 4000 + i],
            1,
            5,
            10,
            INTERVAL '5 seconds'
        );

        IF test_result THEN
            count_true := count_true + 1;
        ELSE
            count_false := count_false + 1;
        END IF;
    END LOOP;

    RETURN;
END $$ LANGUAGE plpgsql;

-- Test 1: Run the test_store_vehicle_data function
-- to insert 15x4 records (node_limit 10, expiring
-- in 5 seconds) into the vehicle_data table and
-- finally store the result in a temporary table
CREATE TEMP TABLE temp_test_result AS
SELECT * FROM test_store_vehicle_data(15);

-- The temp_test_result has just 1 record, check it
SELECT
    CASE
        WHEN count_true != 10 OR count_false != 5
        THEN 'Test 1 failed: expected 10 TRUE, 5 FALSE'
        ELSE 'Test 1 passed'
    END AS test_result
FROM temp_test_result;

-- Drop the temporary table to clean up
DROP TABLE temp_test_result;

-- Pause execution for 10 seconds so that all records expire
SELECT pg_sleep(10);

-- Test 2: Run the test_store_vehicle_data function
-- to insert 15x4 records (node_limit 10, expiring
-- in 5 seconds) into the vehicle_data table and
-- finally store the result in a temporary table
CREATE TEMP TABLE temp_test_result AS
SELECT * FROM test_store_vehicle_data(15);

-- The temp_test_result has just 1 record, check it
SELECT
    CASE
        WHEN count_true != 10 OR count_false != 5
        THEN 'Test 1 failed: expected 10 TRUE, 5 FALSE'
        ELSE 'Test 1 passed'
    END AS test_result
FROM temp_test_result;

-- Drop the temporary table to clean up
DROP TABLE temp_test_result;

-- Print all records in the vehicle_data table
SELECT expires_at < NOW() AS is_expired, *
FROM vehicle_data
ORDER BY container_id;

--000000000000d0ea59062bfb800a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Thank you Giovanni, I did not expect this=
 from NOW() and that is why I was stuck.<br><br>I have decided to keep NOW(=
) in my stored function in the hope it has better performance.<br><br>To fi=
x the issue I have rewritten my smoke tests to be plain SQL without any tra=
nsaction:<br><br><div>CREATE OR REPLACE FUNCTION test_store_vehicle_data(</=
div><div>=C2=A0 =C2=A0 num_runs INTEGER,</div><div>=C2=A0 =C2=A0 OUT count_=
true INTEGER,</div><div>=C2=A0 =C2=A0 OUT count_false INTEGER</div><div>)</=
div><div>RETURNS RECORD AS $$</div><div>DECLARE</div><div>=C2=A0 =C2=A0 tes=
t_result BOOLEAN;</div><div>BEGIN</div><div>=C2=A0 =C2=A0 count_true :=3D 0=
;</div><div>=C2=A0 =C2=A0 count_false :=3D 0;</div><div><br></div><div>=C2=
=A0 =C2=A0 FOR i IN 1..num_runs LOOP</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
-- Store OSM node ids (2 are same, 2 are changing)</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 -- with node limit 10 and retention time 5 seconds</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 test_result :=3D store_vehicle_data(</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 100 + i,=C2=A0</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ARRAY[1000, 2000, 3000 + i, 4000 + i],</=
div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1,=C2=A0</div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 5,=C2=A0</div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 10,=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 INTERVAL &#39;5 seconds&#39;</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 );</div><div><br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF test_resu=
lt THEN</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 count_true :=3D=
 count_true + 1;</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE</div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 count_false :=3D count_false + 1;</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 END IF;</div><div>=C2=A0 =C2=A0 END LOO=
P;</div><div><br></div><div>=C2=A0 =C2=A0 RETURN;</div><div>END $$ LANGUAGE=
 plpgsql;</div><div><br></div><div>-- Test 1: Run the test_store_vehicle_da=
ta function</div><div>-- to insert 15x4 records (node_limit 10, expiring</d=
iv><div>-- in 5 seconds) into the vehicle_data table and</div><div>-- final=
ly store the result in a temporary table</div><div>CREATE TEMP TABLE temp_t=
est_result AS</div><div>SELECT * FROM test_store_vehicle_data(15);</div><di=
v><br></div><div>-- The temp_test_result has just 1 record, check it</div><=
div>SELECT=C2=A0</div><div>=C2=A0 =C2=A0 CASE=C2=A0</div><div>=C2=A0 =C2=A0=
 =C2=A0 =C2=A0 WHEN count_true !=3D 10 OR count_false !=3D 5=C2=A0</div><di=
v>=C2=A0 =C2=A0 =C2=A0 =C2=A0 THEN &#39;Test 1 failed: expected 10 TRUE, 5 =
FALSE&#39;=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE &#39;Test 1 pas=
sed&#39;=C2=A0</div><div>=C2=A0 =C2=A0 END AS test_result</div><div>FROM te=
mp_test_result;</div><div><br></div><div>-- Drop the temporary table to cle=
an up</div><div>DROP TABLE temp_test_result;</div><div><br></div><div>-- Pa=
use execution for 10 seconds so that all records expire</div><div>SELECT pg=
_sleep(10);</div><div><br></div><div>-- Test 2: Run the test_store_vehicle_=
data function</div><div>-- to insert 15x4 records (node_limit 10, expiring<=
/div><div>-- in 5 seconds) into the vehicle_data table and</div><div>-- fin=
ally store the result in a temporary table</div><div>CREATE TEMP TABLE temp=
_test_result AS</div><div>SELECT * FROM test_store_vehicle_data(15);</div><=
div><br></div><div>-- The temp_test_result has just 1 record, check it</div=
><div>SELECT=C2=A0</div><div>=C2=A0 =C2=A0 CASE=C2=A0</div><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 WHEN count_true !=3D 10 OR count_false !=3D 5=C2=A0</div>=
<div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 THEN &#39;Test 1 failed: expected 10 TRUE,=
 5 FALSE&#39;=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE &#39;Test 1 =
passed&#39;=C2=A0</div><div>=C2=A0 =C2=A0 END AS test_result</div><div>FROM=
 temp_test_result;</div><div><br></div><div>-- Drop the temporary table to =
clean up</div><div>DROP TABLE temp_test_result;</div><div><br></div><div>--=
 Print all records in the vehicle_data table</div><div>SELECT expires_at &l=
t; NOW() AS is_expired, *</div><div>FROM vehicle_data</div><div>ORDER BY co=
ntainer_id;</div><div><br></div></div></div>

--000000000000d0ea59062bfb800a--