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

Hi Alex,

On Sat, 18 Jan 2025 at 12:21, Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
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