Postgres Pro
Downloads
Home   >   mailing lists

Thread: What is the maximum length of an IN(a,b,c....d) list in PostgreSQL

What is the maximum length of an IN(a,b,c....d) list in PostgreSQL

From
frank church
Date:
What is the maximum length of an IN(a,b,c....d) list in PostgreSQL?

I am using 7.4.


----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Re: What is the maximum length of an IN(a,b,c....d) list in PostgreSQL

From
Michael Fuhr
Date:
On Fri, Jan 20, 2006 at 01:49:03AM +0000, frank church wrote:
> What is the maximum length of an IN(a,b,c....d) list in PostgreSQL?
>
> I am using 7.4.

In 7.4 and earlier it depends on the max_expr_depth setting.

http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-CLIENT-OTHER

test=> SHOW max_expr_depth;
 max_expr_depth
----------------
 10000
(1 row)

test=> SELECT 1 FROM pg_class WHERE oid IN (1,2,3,4,5,6,7,8,9,10);
 ?column?
----------
(0 rows)

test=> SET max_expr_depth TO 10;
SET
test=> SELECT 1 FROM pg_class WHERE oid IN (1,2,3,4,5,6,7,8,9,10);
ERROR:  expression too complex
DETAIL:  Nesting depth exceeds maximum expression depth 10.
HINT:  Increase the configuration parameter "max_expr_depth".

In 8.0 and later max_expr_depth is gone and the limit depends on
max_stack_depth.

http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-RESOURCE

--
Michael Fuhr

Re: What is the maximum length of an IN(a,b,c....d) list in PostgreSQL

From
Michael Fuhr
Date:
On Fri, Jan 20, 2006 at 02:41:04PM +0000, frank church wrote:
> Is the value the actual length of the IN string, or is the maximum of the comma
> separated exressions?

The number of expressions.  If you set max_expr_depth to 10 then
ten 1-character values cause an error but nine 10000-character
values should be fine (tested on my 7.4.11 system).  I think the
same applies to 8.0 and later with max_stack_depth: the limit depends
on the number of expressions, not on the lengths of the elements.
At least that's what my tests seem to show.

--
Michael Fuhr

Re: What is the maximum length of an IN(a,b,c....d) list in PostgreSQL

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> The number of expressions.  If you set max_expr_depth to 10 then
> ten 1-character values cause an error but nine 10000-character
> values should be fine (tested on my 7.4.11 system).  I think the
> same applies to 8.0 and later with max_stack_depth: the limit depends
> on the number of expressions, not on the lengths of the elements.
> At least that's what my tests seem to show.

Yeah, because the limit is associated with recursion depth in expression
processing.  The actual values of the strings are never on the stack,
only in the heap, so they're not going to affect it.

            regards, tom lane