pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19063: Heavily nesting trivial ROW projections produces out of memory error
3+ messages / 3 participants
[nested] [flat]
* BUG #19063: Heavily nesting trivial ROW projections produces out of memory error
@ 2025-09-24 14:06 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2025-09-24 14:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: lukas.eder@gmail.com
The following bug has been logged on the website:
Bug reference: 19063
Logged by: Lukas Eder
Email address: lukas.eder@gmail.com
PostgreSQL version: 18rc1
Operating system: Linux in Docker in Windows
Description:
Try this query:
select
row (row (row (row (row (row (row (row (
row (row (row (row (row (row (row (row (
row (row (row (row (row (row (row (row (
row (row (row (row (row (row (row (row (
1
))))))))
))))))))
))))))))
)))))))) as "nested";
It produces the following error:
SQL Error [54000]: ERROR: string buffer exceeds maximum allowed length
(1073741823 bytes)
Detail: Cannot enlarge string buffer containing 1073741822 bytes by 1 more
bytes.
I don't understand how such excessive memory consumption is produced by such
a "simple" query. The nesting is a bit excessive, sure, but I think there's
a deeper underlying inefficiency that might be worth addressing.
I've tried both these versions:
- PostgreSQL 17.5 (Debian 17.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
- PostgreSQL 18rc1 (Debian 18~rc1-1.pgdg13+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19063: Heavily nesting trivial ROW projections produces out of memory error
@ 2025-09-24 18:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2025-09-24 18:43 UTC (permalink / raw)
To: lukas.eder@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> I don't understand how such excessive memory consumption is produced by such
> a "simple" query.
Compare
regression=# select row(row(1));
row
---------
("(1)")
(1 row)
regression=# select row(row(row(1)));
row
---------------
("(""(1)"")")
(1 row)
regression=# select row(row(row(row(1))));
row
-------------------------
("(""(""""(1)"""")"")")
(1 row)
Each layer of ROW() has to quote the string representing the next
lower layer, so it doubles all the double-quotes per the quoting
rules at
https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-IO-SYNTAX
So the output length nearly doubles for each layer of ROW(), and yes
it doesn't take that many layers to get to something excessive.
Maybe we could redefine the quoting rules in a way that's friendlier
to this sort of thing, but it would surely make them a lot more
complicated, which would likely introduce enough client-side bugs
to make the change unattractive. Not to mention the whole backwards
compatibility question.
On the whole I can't get excited about this, since I see no very
good reason to use such a data structure.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19063: Heavily nesting trivial ROW projections produces out of memory error
@ 2025-09-25 08:30 Lukas Eder <lukas.eder@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Lukas Eder @ 2025-09-25 08:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Thanks for the explanation. I keep forgetting, even if I've worked on
parsing code many times.
I understand backwards compatibility concerns, but this particular case
does seem to show an extreme waste, especially since I don't think it's
really necessary here. I don't see the ambiguity of nesting parentheses as
(((1))), for example. It appears that the escaping rules have been kept
simple in order to make parsing 1-2 edge cases simpler, at the cost of most
ordinary cases being way too verbose.
Sure, nesting 32 levels without even actual named composite types is not
realistic, as this was just a client side integration test for client side
logic. But nesting 5 levels, including named composite types is not unseen,
and that already incurs 2^4 = 16 unnecessary double quotes for each data
item on each row.
In my opinion, the rich system of user-defined data structures is
PostgreSQL's biggest strength, even compared to Oracle, the other popular
ORDBMS. If there's such a significant performance penalty to using them,
people simply might not.
Of course, there's the workaround of serialising JSON to clients instead of
the native text format...
On Wed, Sep 24, 2025 at 8:43 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > I don't understand how such excessive memory consumption is produced by
> such
> > a "simple" query.
>
> Compare
>
> regression=# select row(row(1));
> row
> ---------
> ("(1)")
> (1 row)
>
>
> regression=# select row(row(row(1)));
> row
> ---------------
> ("(""(1)"")")
> (1 row)
>
> regression=# select row(row(row(row(1))));
> row
> -------------------------
> ("(""(""""(1)"""")"")")
> (1 row)
>
> Each layer of ROW() has to quote the string representing the next
> lower layer, so it doubles all the double-quotes per the quoting
> rules at
>
> https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-IO-SYNTAX
>
> So the output length nearly doubles for each layer of ROW(), and yes
> it doesn't take that many layers to get to something excessive.
>
> Maybe we could redefine the quoting rules in a way that's friendlier
> to this sort of thing, but it would surely make them a lot more
> complicated, which would likely introduce enough client-side bugs
> to make the change unattractive. Not to mention the whole backwards
> compatibility question.
>
> On the whole I can't get excited about this, since I see no very
> good reason to use such a data structure.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-09-25 08:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-24 14:06 BUG #19063: Heavily nesting trivial ROW projections produces out of memory error PG Bug reporting form <noreply@postgresql.org>
2025-09-24 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-09-25 08:30 ` Lukas Eder <lukas.eder@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox