Thread: max_expr_depth
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 What exactly caused this and how do I work around it? -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
I don't know the cause, but if you only have to run this procedure once in a while, you could select all the records that need to be updated, and use a text editor to build a few thousand single update statement, then save this file and echo it to the postgres backend through psql. Good Luck! -r At 08:50 PM 6/18/01 -0400, Joseph Shraibman wrote: >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 > >What exactly caused this and how do I work around it? > > >-- >Joseph Shraibman >jks@selectacast.net >Increase signal to noise ratio. http://www.targabot.com > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl > > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
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 Most likely your IN clause is too big. Try loading those few thousand entries into a temporary table and doing a join. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
The idea was to improve performance by buffering a bunch of single updates and doing a big update .. where in(list). I surmised that it was my in() that was causing the problem, but I wanted to know exactly what the problem was and how big I can make my IN(). Ryan Mahoney wrote: > > I don't know the cause, but if you only have to run this procedure once in > a while, you could select all the records that need to be updated, and use > a text editor to build a few thousand single update statement, then save > this file and echo it to the postgres backend through psql. > > Good Luck! > > -r > > At 08:50 PM 6/18/01 -0400, Joseph Shraibman wrote: > > >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 > > > >What exactly caused this and how do I work around it? > > > > -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Can anyone tell me where to even start looking? Dave
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. I'd recommend sticking the target id values into a temp
table that you can join against, instead.
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
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
Re: Error: RelationBuildTriggers: 2 record(s) not found for rel customerinfo
From
"Dave Cramer"
Date:
Answering my own question here... Shows you that I didn't look hard enough before posting to the list. To fix this problem, update reltriggers.pg_class to the correct number of triggers. Dave ----- Original Message ----- From: "Dave Cramer" <Dave@micro-automation.net> To: <pgsql-general@postgresql.org> Sent: Monday, June 18, 2001 9:35 PM Subject: [GENERAL] Error: RelationBuildTriggers: 2 record(s) not found for rel customerinfo > Can anyone tell me where to even start looking? > > Dave > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
Joseph Shraibman <jks@selectacast.net> writes: > Compared to 1000 updates that took between 25 and 47 seconds, an update > with 1000 itmes in the IN() took less than three seconds. Did you wrap the 1000 separate updates in a transaction? -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
Joseph Shraibman <jks@selectacast.net> writes: > Doug McNaught wrote: > > > > Joseph Shraibman <jks@selectacast.net> writes: > > > > > Compared to 1000 updates that took between 25 and 47 seconds, an update > > > with 1000 itmes in the IN() took less than three seconds. > > > > Did you wrap the 1000 separate updates in a transaction? > > > > -Doug > > No, at a high level in my application I was calling the method to do the > update. How would putting it in a transaction help? If you don't, every update is its own transaction, and Postgres will sync the disks (and wait for the sync to complete) after every one. Doing N updates in one transaction will only sync after the whole transaction is complete. Trust me; it's *way* faster. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
Doug McNaught wrote: > > Joseph Shraibman <jks@selectacast.net> writes: > > > Compared to 1000 updates that took between 25 and 47 seconds, an update > > with 1000 itmes in the IN() took less than three seconds. > > Did you wrap the 1000 separate updates in a transaction? > > -Doug No, at a high level in my application I was calling the method to do the update. How would putting it in a transaction help? -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Doug McNaught wrote: > > Joseph Shraibman <jks@selectacast.net> writes: > > > Doug McNaught wrote: > > > > > > Joseph Shraibman <jks@selectacast.net> writes: > > > > > > > Compared to 1000 updates that took between 25 and 47 seconds, an update > > > > with 1000 itmes in the IN() took less than three seconds. > > > > > > Did you wrap the 1000 separate updates in a transaction? > > > > > > -Doug > > > > No, at a high level in my application I was calling the method to do the > > update. How would putting it in a transaction help? > > If you don't, every update is its own transaction, and Postgres will > sync the disks (and wait for the sync to complete) after every one. > Doing N updates in one transaction will only sync after the whole > transaction is complete. Trust me; it's *way* faster. I thought WAL did away with most of the syncing. Do you really think I should do 1000 updates in a transaction instead of an IN with 1000 items? I can do my buffer flush any way I want but I'd have to think the overhead of making 1000 calls to the backend would be more than overwhelm the cost of the big OR statement (especially if the server and client aren't on the same machine). -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Joseph Shraibman <jks@selectacast.net> writes: > Doug McNaught wrote: > > > > If you don't, every update is its own transaction, and Postgres will > > sync the disks (and wait for the sync to complete) after every one. > > Doing N updates in one transaction will only sync after the whole > > transaction is complete. Trust me; it's *way* faster. > > I thought WAL did away with most of the syncing. Most but not all. It still makes a big difference. Transactions have other overhead, too (housekeeping and such). > Do you really think I should do 1000 updates in a transaction instead of > an IN with 1000 items? I can do my buffer flush any way I want but I'd > have to think the overhead of making 1000 calls to the backend would be > more than overwhelm the cost of the big OR statement (especially if the > server and client aren't on the same machine). Perhaps. The issue for me would be: OK, 1000 entries in an IN works fine. Maybe 2000 works fine. At some point (as you've seen) you hit a limit, whether it's query length, recursion depth or whatever. Then you have to go rewrite your code. I like to do it right the first time. ;) If you know you will never ever have more than N items in the IN clause, and N is demonstrably less than the limit, use IN. "Never ever" is a phrase that often turns out to be false in software devlopment... If you're doing the updates in batches (say, 1000 at a time using IN) you still might want to consider wrapping the whole thing in a transaction. That way, if the client or the network craps out in the middle of the run, you don't have a half-complete set of updates to clean up. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
Doug McNaught wrote: > > The issue for me would be: OK, 1000 entries in an IN works fine. > Maybe 2000 works fine. At some point (as you've seen) you hit a > limit, whether it's query length, recursion depth or whatever. Then > you have to go rewrite your code. I like to do it right the first > time. ;) > > If you know you will never ever have more than N items in the IN > clause, and N is demonstrably less than the limit, use IN. "Never > ever" is a phrase that often turns out to be false in software > devlopment... > > If you're doing the updates in batches (say, 1000 at a time using IN) > you still might want to consider wrapping the whole thing in a > transaction. That way, if the client or the network craps out in the > middle of the run, you don't have a half-complete set of updates to > clean up. Actually is this case I'd prefer to have as much of the updates done as possible. Remember they were single updates that I'm buffering to improve performance. Right now I'm flushing my buffer every minute, and in a minute at our current rate of processing there won't be more than 185 records to update. If I write my buffer after it reaches a limit of 500 I should stay well below the 10000 postgres limit and still save on performance. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Joseph Shraibman <jks@selectacast.net> writes:
> Do you really think I should do 1000 updates in a transaction instead of
> an IN with 1000 items? I can do my buffer flush any way I want but I'd
> have to think the overhead of making 1000 calls to the backend would be
> more than overwhelm the cost of the big OR statement (especially if the
> server and client aren't on the same machine).
If your choices are 1000 separate updates or a 1000-way IN, then maybe
the IN will be faster, but it's likely not as fast as it could be.
The best plan you can hope for from the IN is one indexscan pass per
IN item. You'd get the same sort of plan from the 1000 updates, but
it'd cost 1000 iterations of the parser and planner, so the updates
likely will come out behind.
The real issue is whether you could get a better plan (merge or hash
join, say) from a join to a temp table. Not sure about that --- unless
you go to the trouble of vacuuming the temp table, the planner won't
know much about it and is likely to pick an unhelpful plan anyway.
So maybe you should stick with what you have. You're likely to run into
trouble if you try to scale it to ~ 100000 IN values; that max_expr_depth
check does exist for a reason. But at ~ 1000 values it doesn't sound
too awful.
regards, tom lane