Re: BETWEEN SYMMETRIC condition results in "row is too big: ..., maximum size 8160"

Manuel Rigger <rigger(dot)manuel(at)gmail(dot)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