Обсуждение: "stack depth limit exceeded" executing DELETE FROM

The following fails with a "stack depth limit exceeded":

DELETE FROM some-table WHERE pk IN (pk1, pk2, ..., pkN)

where pk = primary key and N is large, may be > 50000.

Is there some limit as to the number of rows that can
be deleted in a single DELETE?

Thanks,
Brian

"Cox, Brian" <Brian.Cox@ca.com> writes:
> The following fails with a "stack depth limit exceeded":
> DELETE FROM some-table WHERE pk IN (pk1, pk2, ..., pkN)
> where pk = primary key and N is large, may be > 50000.

> Is there some limit as to the number of rows that can
> be deleted in a single DELETE?

Not as such, but there's a limit on the complexity of WHERE clauses.
Raising max_stack_depth might help you, but you'll run out of RAM
eventually.

(FWIW, 8.2 will cope a lot better with this particular construct.)

            regards, tom lane

Not a limit to a single DELETE per se, a single delete could remove
millions of rows, but you can't specify that many individual entries in
an IN (..) section.

This isn't a PG-specific issue by the way; Oracle limits you to about
1000 entries IIRC, and SQL Server limits it to 512 or thereabouts.  It's
just a bad approach.

You want to refactor this to use a transaction and JDBC update batches,
to gain the benefits of batching without the stack overrun problems.  If
you're concerned about speed you could try benchmarking to find the
optimum size for the number of items in the "IN (...)" clause, but in
our testing the optimum number for most databases was 1.  For PG I think
it was 2.  Moral of the story: it's hard to beat individual batch
deletes.

An alternative approach would be to add these doomed PK's to a temporary
table and then do "DELETE FROM myTable WHERE pk IN (SELECT victimId FROM
temp_doomed_table)"

-- Mark

On Tue, 2006-09-05 at 14:18 -0400, Cox, Brian wrote:
> The following fails with a "stack depth limit exceeded":
>
> DELETE FROM some-table WHERE pk IN (pk1, pk2, ..., pkN)
>
> where pk = primary key and N is large, may be > 50000.
>
> Is there some limit as to the number of rows that can
> be deleted in a single DELETE?
>
> Thanks,
> Brian
>
>

On Tue, Sep 05, 2006 at 02:18:05PM -0400, Cox, Brian wrote:
> The following fails with a "stack depth limit exceeded":
>
> DELETE FROM some-table WHERE pk IN (pk1, pk2, ..., pkN)
>
> where pk = primary key and N is large, may be > 50000.
>
> Is there some limit as to the number of rows that can
> be deleted in a single DELETE?

The error isn't due to the number of rows being deleted but rather
from parsing the large number of items in the IN list.  See the
following thread:

http://archives.postgresql.org/pgsql-general/2006-01/msg00959.php

How are you obtaining the N values?  A subquery that returns the
equivalent list should work.  You could also try increasing
max_stack_depth (or max_expr_depth in 7.4 and earlier), but be sure
to stay within your system's stack depth resource limit.

--
Michael Fuhr

On Sep 5, 2006, at 14:44 , Michael Fuhr wrote:

> On Tue, Sep 05, 2006 at 02:18:05PM -0400, Cox, Brian wrote:
>> The following fails with a "stack depth limit exceeded":
>>
>> DELETE FROM some-table WHERE pk IN (pk1, pk2, ..., pkN)
>>
>> where pk = primary key and N is large, may be > 50000.
>>
>> Is there some limit as to the number of rows that can
>> be deleted in a single DELETE?
>
> The error isn't due to the number of rows being deleted but rather
> from parsing the large number of items in the IN list.  See the
> following thread:
>
> http://archives.postgresql.org/pgsql-general/2006-01/msg00959.php

You have been receiving great advice from the other guys, but another
thing to try is to use some middleware to determine which ranges your
delete set covers and use WHERE pk BETWEEN x AND y, potentially
improving index usage performance.

-M