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

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;