| 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 |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | shihao zhong | 2026-11-17 01:01:39 | Re: Fix two race conditions in processing interrupts |