Обсуждение: ERROR: stack depth limit exceeded

Поиск
Список
Период
Сортировка

ERROR: stack depth limit exceeded

От
gzh
Дата:

Hi, 


I upgraded the version of PostgreSQL from 12.13 to 12.16. 

If multiple subqueries or a large number of UNION ALL were used, 

the previously unseen error message "ERROR: stack depth limit exceeded" will appear after upgrading the version.


I understand that increasing the value of max_stack_depth can prevent code errors, 

but is there a good way to find out which SQL statements have issues without having to run all SQL statements, as it would be too expensive?


Regards


Re: ERROR: stack depth limit exceeded

От
Tom Lane
Дата:
gzh <gzhcoder@126.com> writes:
> I upgraded the version of PostgreSQL from 12.13 to 12.16.
> If multiple subqueries or a large number of UNION ALL were used,
> the previously unseen error message "ERROR: stack depth limit exceeded" will appear after upgrading the version.

Please provide a self-contained example.  The people who might be
able to fix this are too busy to guess about what you are seeing.

            regards, tom lane



Re: ERROR: stack depth limit exceeded

От
Geoff Winkless
Дата:
On Thu, 7 Sept 2023 at 08:45, gzh <gzhcoder@126.com> wrote:
> but is there a good way to find out which SQL statements have issues without having to run all SQL statements, as it
wouldbe too expensive?
 

Does your postgresql server log not contain the error with the
statement at issue?

Geoff



Re: ERROR: stack depth limit exceeded

От
gzh
Дата:

I'm sorry I didn't explain the issue clearly. 

Our system is currently running on PostgreSQL 12.13 and we are planning to upgrade to PostgreSQL 12.16. 

We are currently in the evaluation phase. 

In the Release Notes for PostgreSQL 12.14, we saw the following change:

https://www.postgresql.org/docs/release/12.14/


>Add recursion and looping defenses in subquery pullup (Tom Lane)

>

>A contrived query can result in deep recursion and unreasonable amounts of time spent trying to flatten subqueries. A proper fix for that seems unduly invasive for a back-patch, but we can at least add stack depth checks and an interrupt check to allow the query to be cancelled.


Our understanding is that this change will cause some complex SQL statements 

that were previously not reporting errors to report errors in the new version. 

If our understanding of this change is correct, we would like to find out 

which SQL statements will report errors in the new version. 

Do you have any good methods for doing this?





At 2023-09-07 21:29:56, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >gzh <gzhcoder@126.com> writes: >> I upgraded the version of PostgreSQL from 12.13 to 12.16. >> If multiple subqueries or a large number of UNION ALL were used, >> the previously unseen error message "ERROR: stack depth limit exceeded" will appear after upgrading the version. > >Please provide a self-contained example. The people who might be >able to fix this are too busy to guess about what you are seeing. > > regards, tom lane

Re: ERROR: stack depth limit exceeded

От
Laurenz Albe
Дата:
On Fri, 2023-09-08 at 11:32 +0800, gzh wrote:
> I'm sorry I didn't explain the issue clearly. 
> Our system is currently running on PostgreSQL 12.13 and we are planning to upgrade to PostgreSQL 12.16. 
> We are currently in the evaluation phase. 
> In the Release Notes for PostgreSQL 12.14, we saw the following change:
> https://www.postgresql.org/docs/release/12.14/
>
> > Add recursion and looping defenses in subquery pullup (Tom Lane)
> >
> > A contrived query can result in deep recursion and unreasonable amounts of time spent trying to
> > flatten subqueries. A proper fix for that seems unduly invasive for a back-patch, but we can at
> > least add stack depth checks and an interrupt check to allow the query to be cancelled.
>
> Our understanding is that this change will cause some complex SQL statements 
> that were previously not reporting errors to report errors in the new version. 
> If our understanding of this change is correct, we would like to find out 
> which SQL statements will report errors in the new version. 
> Do you have any good methods for doing this?

Asking us to exhaustively describe all queries that could cause errors now
so that you can compare your queries with them is not a good way forward.
Please note that the release notes speak of "contrived queries", so you can
imagine things like

SELECT (
  SELECT (
    SELECT (
       [1000 more such repetitions]
          SELECT 1
       [lots of closing parentheses]

Anyway, the queries that the fix makes throw errors in 12.14 are queries that
would also give you trouble in 12.13, and worse trouble than an error
("unreasonable amounts of time spent").

My advice it not to worry.

But if you feel like worrying very much, go ahead and test your application
thoroughly with 12.14.

Yours,
Laurenz Albe



Re: ERROR: stack depth limit exceeded

От
Tom Lane
Дата:
gzh  <gzhcoder@126.com> writes:
> In the Release Notes for PostgreSQL 12.14, we saw the following change:
> https://www.postgresql.org/docs/release/12.14/

>> Add recursion and looping defenses in subquery pullup (Tom Lane)
>> A contrived query can result in deep recursion and unreasonable amounts of time spent trying to flatten subqueries.
Aproper fix for that seems unduly invasive for a back-patch, but we can at least add stack depth checks and an
interruptcheck to allow the query to be cancelled. 


> Our understanding is that this change will cause some complex SQL statements
> that were previously not reporting errors to report errors in the new version.

The key word there is "contrived".  You are not going to hit this limit
without intentionally trying.  The example that led to adding this check
was a synthetic query with 10000 UNION ALL branches:

https://www.postgresql.org/message-id/flat/703c09a2-08f3-d2ec-b33d-dbecd62428b8%40postgrespro.ru

Also notice that the query misbehaved before this patch, too, by consuming
excessive RAM.

            regards, tom lane



Re: ERROR: stack depth limit exceeded

От
gzh
Дата:

Thank you all for taking the time to help me with my question and offer your advice. Your responses were greatly appreciated!







At 2023-09-08 21:53:33, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >gzh <gzhcoder@126.com> writes: >> In the Release Notes for PostgreSQL 12.14, we saw the following change: >> https://www.postgresql.org/docs/release/12.14/ > >>> Add recursion and looping defenses in subquery pullup (Tom Lane) >>> A contrived query can result in deep recursion and unreasonable amounts of time spent trying to flatten subqueries. A proper fix for that seems unduly invasive for a back-patch, but we can at least add stack depth checks and an interrupt check to allow the query to be cancelled. > > >> Our understanding is that this change will cause some complex SQL statements >> that were previously not reporting errors to report errors in the new version. > >The key word there is "contrived". You are not going to hit this limit >without intentionally trying. The example that led to adding this check >was a synthetic query with 10000 UNION ALL branches: > >https://www.postgresql.org/message-id/flat/703c09a2-08f3-d2ec-b33d-dbecd62428b8%40postgrespro.ru > >Also notice that the query misbehaved before this patch, too, by consuming >excessive RAM. > > regards, tom lane