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

[Alexander Farber]

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;