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:
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;
