MIME-Version: 1.0
From: Alexander Farber <alexander.farber@gmail.com>
Date: Sat, 18 Jan 2025 12:21:18 +0100
Message-ID: <CAADeyWjuNpncDm=pEQfOpLOgc_EQnd6B9sKfxQT8zGKJufcjeQ@mail.gmail.com>
Subject: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in
 my stored function?
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000924312062bf93af1"
Archived-At: <https://www.postgresql.org/message-id/CAADeyWjuNpncDm%3DpEQfOpLOgc_EQnd6B9sKfxQT8zGKJufcjeQ%40mail.gmail.com>
Precedence: bulk

--000000000000924312062bf93af1
Content-Type: text/plain; charset="UTF-8"

Hi fellow PostgreSQL users,

I have prepared a DB Fiddle https://dbfiddle.uk/BCXD_Bo2 for my question
and I will also show my complete SQL code below -

I am trying to store data in a vehicle_data table and each data record has
an expires_at column:

-- Create table for customer IDs
CREATE TABLE customer_ids (
    id INTEGER PRIMARY KEY CHECK (id > 0),
    label TEXT NOT NULL CHECK (label ~ '\S')
);

-- Insert valid customer IDs
INSERT INTO customer_ids (id, label) VALUES
(1, 'Customer 1'),
(2, 'Customer 2'),
(3, 'Customer 3'),
(4, 'Customer 4'),
(5, 'Customer 5');

-- Create table for use case IDs
CREATE TABLE use_case_ids (
    id INTEGER PRIMARY KEY CHECK (id > 0),
    label TEXT NOT NULL CHECK (label ~ '\S')
);

-- Insert valid use case IDs
INSERT INTO use_case_ids (id, label) VALUES
(1, 'Use Case 1'),
(2, 'Use Case 2'),
(3, 'Use Case 3'),
(4, 'Use Case 4'),
(5, 'Use Case 5');

-- Create table for uploaded vehicle data
CREATE TABLE vehicle_data (
    -- the triple is counted when comparing against node_limit
    osm_node_id BIGINT NOT NULL CHECK (osm_node_id > 0),
    customer_id INTEGER NOT NULL,
    use_case_id INTEGER NOT NULL,

    container_id BIGINT NOT NULL CHECK (container_id > 0),
    expires_at TIMESTAMPTZ NOT NULL,

    FOREIGN KEY (customer_id) REFERENCES customer_ids(id),
    FOREIGN KEY (use_case_id) REFERENCES use_case_ids(id),
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- Add a triple-column index to improve search performance
CREATE INDEX idx_vehicle_data ON vehicle_data (osm_node_id, customer_id,
use_case_id);

To fill the vehicle_data table with data I have defined a simple stored
procedure:

CREATE OR REPLACE FUNCTION store_vehicle_data(
    _container_id BIGINT,
    _osm_node_ids BIGINT[],
    _customer_id INTEGER,
    _use_case_id INTEGER,
    _node_limit INTEGER,
    _retention_time INTERVAL
)
RETURNS BOOLEAN AS $$
DECLARE
    _osm_node_id BIGINT;
    _row_count INTEGER;
    _should_send_pull_container BOOLEAN := TRUE;
BEGIN
    -- Delete records with expired retention time - NOT WORKING
    DELETE FROM vehicle_data
    WHERE NOW() > expires_at;

    -- Insert new records
    FOREACH _osm_node_id IN ARRAY _osm_node_ids LOOP
        BEGIN
            INSERT INTO vehicle_data (
                osm_node_id,
                customer_id,
                use_case_id,
                container_id,
                expires_at
            ) VALUES (
                _osm_node_id,
                _customer_id,
                _use_case_id,
                _container_id,
                NOW() + _retention_time
            );
        EXCEPTION WHEN foreign_key_violation THEN
            RAISE EXCEPTION 'Invalid customer_id % or use_case_id % for
osm_node_id % container_id: %',
                _customer_id, _use_case_id, _osm_node_id, _container_id;
        END;

        -- Check if the number of records exceeds the node limit
        SELECT COUNT(*)
        INTO STRICT _row_count
        FROM vehicle_data
        WHERE osm_node_id = _osm_node_id
        AND customer_id = _customer_id
        AND use_case_id = _use_case_id;

        -- There is enough up-to-date vehicle data for this triple,
        -- so tell the vehicle not to send any PULL containers
        IF _row_count > _node_limit THEN
            _should_send_pull_container := FALSE;
        END IF;
    END LOOP;

    RETURN _should_send_pull_container;
END;
$$ LANGUAGE plpgsql;

Finally, I have prepared a smoke test for my code and run it twice:

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;

-- 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.

Thank you for any ideas and I have also asked this question at
https://stackoverflow.com/q/79367012/165071

Best regards
Alex

--000000000000924312062bf93af1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div di=
r=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"lt=
r"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div><div><div><div>H=
i fellow PostgreSQL users,</div><div><br></div><div>I have prepared a DB Fi=
ddle <a href=3D"https://dbfiddle.uk/BCXD_Bo2">https://dbfiddle.uk/BCXD_Bo2<=
/a> for my question and I will also show my complete SQL code below -</div>=
<div><br></div><div>I am trying to store data in a vehicle_data table and e=
ach data record has an expires_at column:</div><div><br></div><div>-- Creat=
e table for customer IDs</div><div>CREATE TABLE customer_ids (</div><div>=
=C2=A0 =C2=A0 id INTEGER PRIMARY KEY CHECK (id &gt; 0),</div><div>=C2=A0 =
=C2=A0 label TEXT NOT NULL CHECK (label ~ &#39;\S&#39;)</div><div>);</div><=
div><br></div><div>-- Insert valid customer IDs</div><div>INSERT INTO custo=
mer_ids (id, label) VALUES</div><div>(1, &#39;Customer 1&#39;),</div><div>(=
2, &#39;Customer 2&#39;),</div><div>(3, &#39;Customer 3&#39;),</div><div>(4=
, &#39;Customer 4&#39;),</div><div>(5, &#39;Customer 5&#39;);</div><div><br=
></div><div>-- Create table for use case IDs</div><div>CREATE TABLE use_cas=
e_ids (</div><div>=C2=A0 =C2=A0 id INTEGER PRIMARY KEY CHECK (id &gt; 0),</=
div><div>=C2=A0 =C2=A0 label TEXT NOT NULL CHECK (label ~ &#39;\S&#39;)</di=
v><div>);</div><div><br></div><div>-- Insert valid use case IDs</div><div>I=
NSERT INTO use_case_ids (id, label) VALUES</div><div>(1, &#39;Use Case 1=
9;),</div><div>(2, &#39;Use Case 2&#39;),</div><div>(3, &#39;Use Case 3&#39=
;),</div><div>(4, &#39;Use Case 4&#39;),</div><div>(5, &#39;Use Case 5&#39;=
);</div><div><br></div><div>-- Create table for uploaded vehicle data</div>=
<div>CREATE TABLE vehicle_data (</div><div>=C2=A0 =C2=A0 -- the triple is c=
ounted when comparing against node_limit</div><div>=C2=A0 =C2=A0 osm_node_i=
d BIGINT NOT NULL CHECK (osm_node_id &gt; 0),</div><div>=C2=A0 =C2=A0 custo=
mer_id INTEGER NOT NULL,</div><div>=C2=A0 =C2=A0 use_case_id INTEGER NOT NU=
LL,</div><div><br></div><div>=C2=A0 =C2=A0 container_id BIGINT NOT NULL CHE=
CK (container_id &gt; 0),</div><div>=C2=A0 =C2=A0 expires_at TIMESTAMPTZ NO=
T NULL,</div><div><br></div><div>=C2=A0 =C2=A0 FOREIGN KEY (customer_id) RE=
FERENCES customer_ids(id),</div><div>=C2=A0 =C2=A0 FOREIGN KEY (use_case_id=
) REFERENCES use_case_ids(id),</div><div>=C2=A0 =C2=A0 id BIGINT GENERATED =
ALWAYS AS IDENTITY PRIMARY KEY</div><div>);</div><div><br></div><div>-- Add=
 a triple-column index to improve search performance</div><div>CREATE INDEX=
 idx_vehicle_data ON vehicle_data (osm_node_id, customer_id, use_case_id);<=
/div><div><br></div><div>To fill the vehicle_data table with data I have de=
fined a simple stored procedure:</div><div><br></div><div>CREATE OR REPLACE=
 FUNCTION store_vehicle_data(</div><div>=C2=A0 =C2=A0 _container_id BIGINT,=
</div><div>=C2=A0 =C2=A0 _osm_node_ids BIGINT[],</div><div>=C2=A0 =C2=A0 _c=
ustomer_id INTEGER,</div><div>=C2=A0 =C2=A0 _use_case_id INTEGER,</div><div=
>=C2=A0 =C2=A0 _node_limit INTEGER,</div><div>=C2=A0 =C2=A0 _retention_time=
 INTERVAL</div><div>)</div><div>RETURNS BOOLEAN AS $$</div><div>DECLARE</di=
v><div>=C2=A0 =C2=A0 _osm_node_id BIGINT;</div><div>=C2=A0 =C2=A0 _row_coun=
t INTEGER;</div><div>=C2=A0 =C2=A0 _should_send_pull_container BOOLEAN :=3D=
 TRUE;</div><div>BEGIN</div><div>=C2=A0 =C2=A0 -- Delete records with expir=
ed retention time - NOT WORKING</div><div>=C2=A0 =C2=A0 DELETE FROM vehicle=
_data</div><div>=C2=A0 =C2=A0 WHERE NOW() &gt; expires_at;</div><div><br></=
div><div>=C2=A0 =C2=A0 -- Insert new records</div><div>=C2=A0 =C2=A0 FOREAC=
H _osm_node_id IN ARRAY _osm_node_ids LOOP</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 BEGIN</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INT=
O vehicle_data (</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 osm_node_id,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 customer_id,=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 use_case_id,=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 container_id,=C2=A0</div><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 expires_at</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ) VALUES (</div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 _osm_node_id,=C2=A0</div><div>=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 _customer_id,=C2=A0</div>=
<div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 _use_case_id,=
=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 _c=
ontainer_id,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 NOW() + _retention_time</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 );</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 EXCEPTION WHEN foreign_key_=
violation THEN</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 RAISE EX=
CEPTION &#39;Invalid customer_id % or use_case_id % for osm_node_id % conta=
iner_id: %&#39;,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 _customer_id, _use_case_id, _osm_node_id, _container_id;</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END;</div><div><br></div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 -- Check if the number of records exceeds the node limit</div><d=
iv>=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT COUNT(*)</div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 INTO STRICT _row_count</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 FRO=
M vehicle_data</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE osm_node_id =3D =
_osm_node_id</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND customer_id =3D _cus=
tomer_id</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND use_case_id =3D _use_cas=
e_id;</div><div><br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- There is enou=
gh up-to-date vehicle data for this triple,</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 -- so tell the vehicle not to send any PULL containers</div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 IF _row_count &gt; _node_limit THEN</div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 _should_send_pull_container :=3D FAL=
SE;</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 END IF;</div><div>=C2=A0 =C2=A0 E=
ND LOOP;</div><div><br></div><div>=C2=A0 =C2=A0 RETURN _should_send_pull_co=
ntainer;</div><div>END;</div><div>$$ LANGUAGE plpgsql;</div><div><br></div>=
<div>Finally, I have prepared a smoke test for my code and run it twice:</d=
iv><div><br></div><div>CREATE OR REPLACE FUNCTION test_store_vehicle_data(<=
/div><div>=C2=A0 =C2=A0 num_runs INTEGER,</div><div>=C2=A0 =C2=A0 OUT count=
_true INTEGER,</div><div>=C2=A0 =C2=A0 OUT count_false INTEGER</div><div>)<=
/div><div>RETURNS RECORD AS $$</div><div>DECLARE</div><div>=C2=A0 =C2=A0 te=
st_result BOOLEAN;</div><div>BEGIN</div><div>=C2=A0 =C2=A0 count_true :=3D =
0;</div><div>=C2=A0 =C2=A0 count_false :=3D 0;</div><div><br></div><div>=C2=
=A0 =C2=A0 FOR i IN 1..num_runs LOOP</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
-- Store OSM node ids (2 are same, 2 are changing)</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 -- with node limit 10 and retention time 5 seconds</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 test_result :=3D store_vehicle_data(</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 100 + i,=C2=A0</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ARRAY[1000, 2000, 3000 + i, 4000 + i],</=
div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1,=C2=A0</div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 5,=C2=A0</div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 10,=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 INTERVAL &#39;5 seconds&#39;</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 );</div><div><br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF test_resu=
lt THEN</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 count_true :=3D=
 count_true + 1;</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE</div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 count_false :=3D count_false + 1;</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 END IF;</div><div>=C2=A0 =C2=A0 END LOO=
P;</div><div><br></div><div>=C2=A0 =C2=A0 RETURN;</div><div>END $$ LANGUAGE=
 plpgsql;</div><div><br></div><div>-- Run 2 smoke tests</div><div>DO $$</di=
v><div>DECLARE</div><div>=C2=A0 =C2=A0 test_result RECORD;</div><div>BEGIN<=
/div><div>=C2=A0 =C2=A0 -- Test 1: store 15x4 records expiring in 5 seconds=
</div><div>=C2=A0 =C2=A0 SELECT * INTO test_result FROM test_store_vehicle_=
data(15);</div><div>=C2=A0 =C2=A0 IF test_result.count_true !=3D 10 OR test=
_result.count_false !=3D 5 THEN</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 RAISE=
 EXCEPTION &#39;Test 1 failed: expected 10 TRUE, 5 FALSE&#39;;</div><div>=
=C2=A0 =C2=A0 END IF;</div><div><br></div><div>=C2=A0 =C2=A0 -- Sleep for 1=
0 seconds, so that all records in vehicle_data expire</div><div>=C2=A0 =C2=
=A0 PERFORM pg_sleep(10);</div><div><br></div><div>=C2=A0 =C2=A0 -- Test 2:=
 store 15x4 records expiring in 5 seconds</div><div>=C2=A0 =C2=A0 SELECT * =
INTO test_result FROM test_store_vehicle_data(15);</div><div>=C2=A0 =C2=A0 =
IF test_result.count_true !=3D 10 OR test_result.count_false !=3D 5 THEN</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 RAISE EXCEPTION &#39;Test 2 failed: exp=
ected 10 TRUE, 5 FALSE&#39;;</div><div>=C2=A0 =C2=A0 END IF;</div><div>END =
$$;</div><div><br></div><div>-- Print all records in the vehicle_data table=
</div><div>SELECT expires_at &lt; NOW() AS expired, * FROM vehicle_data ORD=
ER BY container_id;</div><div><br></div><div>My problem is that the DELETE =
FROM vehicle_data WHERE NOW() &gt; expires_at; statement in my store_vehicl=
e_data() function does not see to delete anything.</div><div><br></div><div=
>And then the SELECT expires_at &lt; NOW() AS expired, * FROM vehicle_data =
ORDER BY container_id; prints the records in the table and yes, they are al=
l expired there.</div><div><br></div><div>I run the above SQL code (creatin=
g tables, creating functions, running smoke test) in a Dockerfile based on =
the official Postgres Dockerfile and the smoke test (the Test 2) just alway=
s fails.</div><div><br></div><div>Then I connect using psql to my docker co=
ntainer and run the DELETE command at the psql prompt and voila - it works =
there as expected and deletes all the expired records.</div><div><br></div>=
<div>Thank you for any ideas and I have also asked this question at <a href=
=3D"https://stackoverflow.com/q/79367012/165071">https://stackoverflow.com/=
q/79367012/165071</a></div><div><br></div><div>Best regards</div><div>Alex<=
/div></div></div></div><div><br></div></div></div></div></div></div></div><=
/div>
</div></div></div></div></div>

--000000000000924312062bf93af1--