Hi Alex, On Sat, 18 Jan 2025 at 12:21, Alexander Farber <alexander.far...@gmail.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
