public inbox for [email protected]  

help / color / mirror / Atom feed

From: GF <[email protected]>
To: Alexander Farber <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function?
Date: Sat, 18 Jan 2025 13:05:20 +0100
Message-ID: <CAFePLY0G3UmzdbfdJs5Ngdo4GoQKpSW2okfp00hvpm-HBkGYRQ@mail.gmail.com> (raw)
In-Reply-To: <CAADeyWjuNpncDm=pEQfOpLOgc_EQnd6B9sKfxQT8zGKJufcjeQ@mail.gmail.com>
References: <CAADeyWjuNpncDm=pEQfOpLOgc_EQnd6B9sKfxQT8zGKJufcjeQ@mail.gmail.com>

Hi Alex,

On Sat, 18 Jan 2025 at 12:21, Alexander Farber <[email protected]>
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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function?
  In-Reply-To: <CAFePLY0G3UmzdbfdJs5Ngdo4GoQKpSW2okfp00hvpm-HBkGYRQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox