Thread: BETWEEN SYMMETRIC condition results in "row is too big: ..., maximumsize 8160"
BETWEEN SYMMETRIC condition results in "row is too big: ..., maximumsize 8160"
From
Manuel Rigger
Date:
Hi everyone, I noticed that indexes that use nested BETWEEN SYMMETRIC operators quickly exceed some limit, which is indicated by errors such as "ERROR: row is too big: size 8440, maximum size 8160". For example, consider the following (contrived) index: CREATE TABLE t0(c0 TEXT); CREATE INDEX i0 ON t0(c0) WHERE ((FALSE BETWEEN SYMMETRIC TRUE AND (t0.c0 || t0.c0 IN (t0.c0, t0.c0, '1', t0.c0 || t0.c0, t0.c0 || t0.c0 || t0.c0))) BETWEEN SYMMETRIC TRUE AND TRUE) BETWEEN SYMMETRIC TRUE AND TRUE; -- ERROR: row is too big: size 8440, maximum size 8160 Is this an expected limitation? Best, Manuel
Re: BETWEEN SYMMETRIC condition results in "row is too big: ..., maximum size 8160"
From
Tom Lane
Date:
Manuel Rigger <rigger.manuel@gmail.com> writes: > I noticed that indexes that use nested BETWEEN SYMMETRIC operators > quickly exceed some limit, which is indicated by errors such as > "ERROR: row is too big: size 8440, maximum size 8160". For example, > consider the following (contrived) index: > CREATE TABLE t0(c0 TEXT); > CREATE INDEX i0 ON t0(c0) WHERE ((FALSE BETWEEN SYMMETRIC TRUE AND > (t0.c0 || t0.c0 IN (t0.c0, t0.c0, '1', t0.c0 || t0.c0, t0.c0 || t0.c0 > || t0.c0))) BETWEEN SYMMETRIC TRUE AND TRUE) BETWEEN SYMMETRIC TRUE > AND TRUE; -- ERROR: row is too big: size 8440, maximum size 8160 It's not specifically BETWEEN SYMMETRIC that's at issue --- any sufficiently complex index WHERE condition or index expression will do it. (BETWEEN SYMMETRIC helps things along because it duplicates its subexpressions, though.) > Is this an expected limitation? Yeah. Those expressions are stored in pg_index, which doesn't have a TOAST table so there's a hard limit on its row width. Adding a TOAST table would create some interesting circularity issues, and there doesn't seem to be that much real-world use for very complex index expressions or predicates, so we haven't tried to fix it. (If you're desperate for some way to overcome the limit, though, you can do so by creating a SQL function and using it in the index expression.) If you're interested, there was some discussion not too long ago about which system catalogs should have TOAST tables: https://www.postgresql.org/message-id/flat/84ddff04-f122-784b-b6c5-3536804495f8%40joeconway.com regards, tom lane