PostgreSQL 2203D Error: Causes and Solutions Complete Guide

-- Problematic: inserting a huge array directly
INSERT INTO sensor_data (device_id, readings)
VALUES (1, '[...millions of elements...]'::jsonb);

-- Check array size before inserting
SELECT jsonb_array_length(
    '["elem1","elem2","elem3"]'::jsonb
) AS array_size;

-- Safe insert with size guard
DO $$
DECLARE
    v_data JSONB := '[...your data...]'::jsonb;
BEGIN
    IF jsonb_array_length(v_data) > 100000 THEN
        RAISE EXCEPTION 'Array too large: % elements', jsonb_array_length(v_data)
              USING ERRCODE = '2203D';
    END IF;
    INSERT INTO sensor_data (device_id, readings) VALUES (1, v_data);
END;
$$;

-- Problematic: aggregating an entire large table
SELECT json_agg(t) FROM orders t;  -- Can fail with 2203D

-- Fix: limit the result set before aggregating
SELECT json_agg(sub)
FROM (
    SELECT id, customer_id, total_amount, created_at
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '24 hours'
    ORDER BY created_at DESC
    LIMIT 5000
) sub;

-- Fix: paginate aggregation in a loop
DO $$
DECLARE
    v_offset INT := 0;
    v_limit  INT := 5000;
    v_chunk  JSONB;
BEGIN
    LOOP
        SELECT jsonb_agg(row_to_json(t)::jsonb)
        INTO v_chunk
        FROM (
            SELECT id, customer_id, total_amount
            FROM orders
            ORDER BY id
            LIMIT v_limit OFFSET v_offset
        ) t;

        EXIT WHEN v_chunk IS NULL;

        INSERT INTO export_chunks (offset_index, data)
        VALUES (v_offset, v_chunk);

        v_offset := v_offset + v_limit;
    END LOOP;
END;
$$;

-- Identify offending rows first
SELECT id,
       jsonb_array_length(payload) AS array_size
FROM event_logs
WHERE jsonb_typeof(payload) = 'array'
ORDER BY array_size DESC
LIMIT 10;

-- Safe unnest: process only the first N elements
SELECT elem
FROM jsonb_array_elements(
    (SELECT payload FROM event_logs WHERE id = 42)
) WITH ORDINALITY AS t(elem, idx)
WHERE idx <= 10000;  -- cap at 10,000 elements

-- Reusable safe wrapper function
CREATE OR REPLACE FUNCTION safe_unnest_json(p_data JSONB, p_max INT DEFAULT 10000)
RETURNS SETOF JSONB LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT elem
    FROM jsonb_array_elements(p_data) WITH ORDINALITY AS t(elem, idx)
    WHERE idx <= p_max;
END;
$$;

ALTER TABLE event_logs
ADD CONSTRAINT chk_payload_size
CHECK (
    jsonb_typeof(payload) != 'array'
    OR jsonb_array_length(payload) <= 10000
);

-- Cursor-based safe processing
BEGIN;
DECLARE big_cur CURSOR FOR
    SELECT id, payload FROM event_logs WHERE jsonb_typeof(payload) = 'array';
FETCH 1000 FROM big_cur;
-- process batch, then FETCH next 1000...
COMMIT;

-- Weekly monitoring: find rows with large JSON arrays
SELECT id,
       jsonb_array_length(payload) AS arr_size
FROM event_logs
WHERE jsonb_typeof(payload) = 'array'
  AND jsonb_array_length(payload) > 5000
ORDER BY arr_size DESC;