Tom Lane wrote:
>
> Joseph Shraibman <jks@selectacast.net> writes:
> > I recently tried to do a big update with postgres 7.1.2. The update was
> > something like
> > UPDATE table SET status = 2 WHERE id IN (a few thousand entries) AND
> > status = 1;
> > and I got:
> > ERROR: Expression too complex: nesting depth exceeds max_expr_depth =
> > 10000
>
> How many is "a few thousand"? About 10000 by any chance? That
> "IN (a, b, ...)" will expand to "((id = a) OR (id = b) OR ...)"
> which would set off the expression-too-complex detector right about
> 10000 ORs, if I'm not mistaken.
>
> You could crank up the max_expr_depth SET variable if you are so
> inclined, but frankly performance of this query is going to suck
> anyway.
Compared to 1000 updates that took between 25 and 47 seconds, an update
with 1000 itmes in the IN() took less than three seconds.
I'd recommend sticking the target id values into a temp
> table that you can join against, instead.
>
Then I'd have to insert them all into the temp table and do the join,
which would defeat the purpose of my having a buffer to make one call to
postgres.
But shouldn't IN() be smarter? The contents of the IN() are matched
against the primary key of the table, postgres should be able to do a
join-like operation to do the selecting. It is using an index now
according to EXPLAIN.
> As for why we have an expression-too-complex check, it's because
> mysql's crashme test used to provoke a stack overflow crash...
>
> regards, tom lane
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com