Subject: Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function?

From: Alexander Farber <[email-masked]>
Date: Sat, 18 Jan 2025 15:04:07 +0100

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;

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 18 Jan 2025 08:06:29 -0800

On 1/18/25 06:04, Alexander Farber wrote:
> Thank you Giovanni, I did not expect this from NOW() and that is why I 
> was stuck.

FYI, it is explained here:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

-- 
Adrian Klaver
[email-masked]