Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

-- 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);

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

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;

CREATE TABLE

INSERT 0 5

CREATE TABLE

INSERT 0 5

CREATE TABLE

CREATE INDEX

CREATE FUNCTION

CREATE FUNCTION

ERROR:  Test 2 failed: expected 10 TRUE, 5 FALSE
CONTEXT:  PL/pgSQL function inline_code_block line 17 at RAISE