[PATCH] Avoid quadratic rescans in plpgsql_location_to_lineno()

From: Bjorn Pagen <hello(at)bjornpagen(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: [PATCH] Avoid quadratic rescans in plpgsql_location_to_lineno()
Date: 2026-11-17 01:10:27
Message-ID: CAARz7_iL74pU_ghjhjHnqUpHuaUL48uRcv=Xak+0uRgSkys6YA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Compiling a PL/pgSQL function is quadratic in the number of IF, ELSIF
and CASE WHEN statements it has. The grammar asks for their line
numbers only after parsing their bodies, so the location goes
backwards, and plpgsql_location_to_lineno() then rescans the source
from the start.

A CASE with 40000 WHEN arms takes about 15 s to compile here, both at
CREATE FUNCTION and at the first call in each session:

SET check_function_bodies = on;
\timing on
SELECT format('CREATE FUNCTION f(k int) RETURNS int LANGUAGE plpgsql AS %L',
E'BEGIN\n CASE k\n'
|| string_agg(format(E' WHEN %s THEN\n RETURN %s;', i, i), E'\n')
|| E'\n END CASE;\nEND')
FROM generate_series(1, 40000) i \gexec

The attached patch walks the current line back instead, which brings
that down to about 80 ms. It also adds a test of the reported line
numbers, since nothing in the regression tests checks an error at an
IF.

We ran into this with generated trigger functions.

Thanks,
Bjorn Pagen

Attachment Content-Type Size
v1-0001-Avoid-quadratic-rescans-in-plpgsql_location_to_li.patch text/x-patch 5.3 KB

Browse pgsql-hackers by date

  From Date Subject
Previous Message shihao zhong 2026-11-17 01:01:39 Re: Fix two race conditions in processing interrupts