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

[GF <phabriz@xxxxxxxxx>]

Hi Alex,

On Sat, 18 Jan 2025 at 12:21, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

[...]

-- Run 2 smoke tests

DO $$

DECLARE

    test_result RECORD;

BEGIN

    -- Test 1: store 15x4 records expiring in 5 seconds

    SELECT * INTO test_result FROM test_store_vehicle_data(15);

    IF test_result.count_true != 10 OR test_result.count_false != 5 THEN

        RAISE EXCEPTION 'Test 1 failed: expected 10 TRUE, 5 FALSE';

    END IF;

    -- Sleep for 10 seconds, so that all records in vehicle_data expire

    PERFORM pg_sleep(10);

    -- Test 2: store 15x4 records expiring in 5 seconds

    SELECT * INTO test_result FROM test_store_vehicle_data(15);

    IF test_result.count_true != 10 OR test_result.count_false != 5 THEN

        RAISE EXCEPTION 'Test 2 failed: expected 10 TRUE, 5 FALSE';

    END IF;

END $$;

-- Print all records in the vehicle_data table

SELECT expires_at < NOW() AS expired, * FROM vehicle_data ORDER BY container_id;

My problem is that the DELETE FROM vehicle_data WHERE NOW() > expires_at; statement in my store_vehicle_data() function does not see to delete anything.

And then the SELECT expires_at < NOW() AS expired, * FROM vehicle_data ORDER BY container_id; prints the records in the table and yes, they are all expired there.

I run the above SQL code (creating tables, creating functions, running smoke test) in a Dockerfile based on the official Postgres Dockerfile and the smoke test (the Test 2) just always fails.

Then I connect using psql to my docker container and run the DELETE command at the psql prompt and voila - it works there as expected and deletes all the expired records.

The function now() returns the timestamp of the transaction start, so it will be stable during all your "do $$ ...end; $$;" smoke test toplevel block: i.e., it will return the same value across the two test_store_vehicle_data() invocations, so the predicate "where NOW() > expires_at" in store_vehicle_data() will return no rows. That's also why running a second time the delete command it worked: you were running it in another transaction after more than 10 seconds from the first one.

If you want to keep a stable semantics (i.e., still use now() in your store_vehicle_data() function), then you should split your smoke test in two distinct "do $$ end; $$;" invocations, one for the first call to test_store_vehicle_data() with pg_sleep(), and one for the second call.

Otherwise you could use clock_timestamp() instead of now() inside store_vehicle_data(), as this returns the actual current timestamp (maybe storing it in a variable _x to be used in the "where _x > expires_at" condition, to avoid the "sliding predicate" effect of "where clock_timestamp() > expires_at").

Best,

giovanni