public inbox for [email protected]  

help / color / mirror / Atom feed

Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function?
2+ messages / 2 participants
[nested] [flat]

* Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function?
@ 2025-01-18 14:04  Alexander Farber <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Alexander Farber @ 2025-01-18 14:04 UTC (permalink / raw)
  To: ; +Cc: pgsql-general

Thank you Giovanni, I did not expect this from NOW() and that is why I was
stuck.

I have decided to keep NOW() in my stored function in the hope it has
better performance.

To fix the issue I have rewritten my smoke tests to be plain SQL without
any transaction:

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;

-- Test 1: Run the test_store_vehicle_data function
-- to insert 15x4 records (node_limit 10, expiring
-- in 5 seconds) into the vehicle_data table and
-- finally store the result in a temporary table
CREATE TEMP TABLE temp_test_result AS
SELECT * FROM test_store_vehicle_data(15);

-- The temp_test_result has just 1 record, check it
SELECT
    CASE
        WHEN count_true != 10 OR count_false != 5
        THEN 'Test 1 failed: expected 10 TRUE, 5 FALSE'
        ELSE 'Test 1 passed'
    END AS test_result
FROM temp_test_result;

-- Drop the temporary table to clean up
DROP TABLE temp_test_result;

-- Pause execution for 10 seconds so that all records expire
SELECT pg_sleep(10);

-- Test 2: Run the test_store_vehicle_data function
-- to insert 15x4 records (node_limit 10, expiring
-- in 5 seconds) into the vehicle_data table and
-- finally store the result in a temporary table
CREATE TEMP TABLE temp_test_result AS
SELECT * FROM test_store_vehicle_data(15);

-- The temp_test_result has just 1 record, check it
SELECT
    CASE
        WHEN count_true != 10 OR count_false != 5
        THEN 'Test 1 failed: expected 10 TRUE, 5 FALSE'
        ELSE 'Test 1 passed'
    END AS test_result
FROM temp_test_result;

-- Drop the temporary table to clean up
DROP TABLE temp_test_result;

-- Print all records in the vehicle_data table
SELECT expires_at < NOW() AS is_expired, *
FROM vehicle_data
ORDER BY container_id;


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function?
@ 2025-01-18 16:06  Adrian Klaver <[email protected]>
  parent: Alexander Farber <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Adrian Klaver @ 2025-01-18 16:06 UTC (permalink / raw)
  To: Alexander Farber <[email protected]>; +Cc: pgsql-general

On 1/18/25 06:04, Alexander Farber wrote:
> Thank you Giovanni, I did not expect this from NOW() and that is why I 
> was stuck.

FYI, it is explained here:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT



-- 
Adrian Klaver
[email protected]







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-01-18 16:06 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-18 14:04 Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function? Alexander Farber <[email protected]>
2025-01-18 16:06 ` Adrian Klaver <[email protected]>

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