pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: stack depth limit exceeded
14+ messages / 7 participants
[nested] [flat]
* ERROR: stack depth limit exceeded
@ 2023-09-07 07:45 gzh <gzhcoder@126.com>
2023-09-07 13:29 ` Re: ERROR: stack depth limit exceeded Tom Lane <tgl@sss.pgh.pa.us>
2023-09-07 13:46 ` Re: ERROR: stack depth limit exceeded Geoff Winkless <pgsqladmin@geoff.dj>
0 siblings, 2 replies; 14+ messages in thread
From: gzh @ 2023-09-07 07:45 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2023-09-07 07:45 ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
@ 2023-09-07 13:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-08 03:32 ` Re: ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
1 sibling, 1 reply; 14+ messages in thread
From: Tom Lane @ 2023-09-07 13:29 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2023-09-07 07:45 ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
2023-09-07 13:29 ` Re: ERROR: stack depth limit exceeded Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-09-08 03:32 ` gzh <gzhcoder@126.com>
2023-09-08 06:40 ` Re: ERROR: stack depth limit exceeded Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-08 13:53 ` Re: ERROR: stack depth limit exceeded Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 14+ messages in thread
From: gzh @ 2023-09-08 03:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2023-09-07 07:45 ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
2023-09-07 13:29 ` Re: ERROR: stack depth limit exceeded Tom Lane <tgl@sss.pgh.pa.us>
2023-09-08 03:32 ` Re: ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
@ 2023-09-08 06:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2023-09-08 06:40 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2023-09-07 07:45 ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
2023-09-07 13:29 ` Re: ERROR: stack depth limit exceeded Tom Lane <tgl@sss.pgh.pa.us>
2023-09-08 03:32 ` Re: ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
@ 2023-09-08 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-11 04:57 ` Re: ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
1 sibling, 1 reply; 14+ messages in thread
From: Tom Lane @ 2023-09-08 13:53 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2023-09-07 07:45 ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
2023-09-07 13:29 ` Re: ERROR: stack depth limit exceeded Tom Lane <tgl@sss.pgh.pa.us>
2023-09-08 03:32 ` Re: ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
2023-09-08 13:53 ` Re: ERROR: stack depth limit exceeded Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-09-11 04:57 ` gzh <gzhcoder@126.com>
0 siblings, 0 replies; 14+ messages in thread
From: gzh @ 2023-09-11 04:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2023-09-07 07:45 ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
@ 2023-09-07 13:46 ` Geoff Winkless <pgsqladmin@geoff.dj>
1 sibling, 0 replies; 14+ messages in thread
From: Geoff Winkless @ 2023-09-07 13:46 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: pgsql-general@lists.postgresql.org
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 would be too expensive?
Does your postgresql server log not contain the error with the
statement at issue?
Geoff
^ permalink raw reply [nested|flat] 14+ messages in thread
* ERROR: stack depth limit exceeded
@ 2025-02-19 10:23 Csányi Pál <csanyipal@gmail.com>
2025-02-19 11:22 ` Re: ERROR: stack depth limit exceeded Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 14:31 ` Re: ERROR: stack depth limit exceeded Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Csányi Pál @ 2025-02-19 10:23 UTC (permalink / raw)
To: general pgsql <pgsql-general@lists.postgresql.org>
Hi,
I am running postgresql 15 on Debian 12 operating system.
I also installed PgAdmin4 and am using the desktop version of it.
I have created a trigger function and a trigger which will run the
following code, see below.
Before this trigger would run, I tried this code.
When running the code I get the following error message:
ERROR: stack depth limit exceeded HINT: Increase the configuration
parameter "max_stack_depth" (currently 2048kB), after ensuring the
platform's stack depth limit is adequate.
Where am I making a mistake, and what can I do to make this code and
this trigger run successfully?
-- Insert the RSD saved between my last two pensions in the
haavi_megtakaritasaink table.
-- INSERT INTO x(a) VALUES (tsrange($1, $2, '[)'));
-- Includes the first date [, the second date is not ).
INSERT INTO
vagyonunk_kezelese.public.havonkenti_megtakaritasaink
(ezen_idokozben, megtakaritva_rsd, kelt)
VALUES ((select tsrange((
SELECT
kelt
FROM
javaink_forgalma
WHERE
EN_KAPTAM = 'true'
AND AZ_ARU_NEVE = 'nyugdíjam'
ORDER BY
KELT DESC
LIMIT
1
OFFSET
1
),(
SELECT
KELT
FROM
JAVAINK_FORGALMA
WHERE
EN_KAPTAM = 'true'
AND AZ_ARU_NEVE = 'nyugdíjam'
ORDER BY
KELT DESC
LIMIT
1
OFFSET
0
),'[)')),(SELECT
SUM(EGYSEGAR * ENNYI_EGYSEG)
FROM
JAVAINK_FORGALMA
WHERE
A_PENZNEM = 'RSD'
AND KELT >= (
SELECT
KELT
FROM
JAVAINK_FORGALMA
WHERE
EN_KAPTAM = 'true'
AND AZ_ARU_NEVE = 'nyugdíjam'
ORDER BY
KELT DESC
LIMIT
1
OFFSET
1
)
AND KELT < (
SELECT
KELT
FROM
JAVAINK_FORGALMA
WHERE
EN_KAPTAM = 'true'
AND AZ_ARU_NEVE = 'nyugdíjam'
ORDER BY
KELT DESC
LIMIT
1
OFFSET
0
)), DEFAULT)
;
Thanks for all the advice!
--
Best wishes by Paul Chanyi!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2025-02-19 10:23 ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
@ 2025-02-19 11:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:49 ` Re: ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Laurenz Albe @ 2025-02-19 11:22 UTC (permalink / raw)
To: csanyipal@gmail.com, general pgsql <pgsql-general@lists.postgresql.org>
On Wed, 2025-02-19 at 11:23 +0100, Csányi Pál wrote:
> I am running postgresql 15 on Debian 12 operating system.
> I also installed PgAdmin4 and am using the desktop version of it.
> I have created a trigger function and a trigger which will run the
> following code, see below.
> Before this trigger would run, I tried this code.
> When running the code I get the following error message:
> ERROR: stack depth limit exceeded HINT: Increase the configuration
> parameter "max_stack_depth" (currently 2048kB), after ensuring the
> platform's stack depth limit is adequate.
>
> Where am I making a mistake, and what can I do to make this code and
> this trigger run successfully?
You don't show the entire functoin and the trigger definition,
but from the symptoms it is pretty clear that the trigger is
modifying the table on which it is defined. That will call the
trigger again and lead to an endless recursion, which leads to
the observed error.
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2025-02-19 10:23 ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
2025-02-19 11:22 ` Re: ERROR: stack depth limit exceeded Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-02-19 15:49 ` Csányi Pál <csanyipal@gmail.com>
2025-02-19 16:09 ` Re: ERROR: stack depth limit exceeded Sándor Daku <daku.sandor@gmail.com>
2025-02-20 09:33 ` Re: ERROR: stack depth limit exceeded Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 14+ messages in thread
From: Csányi Pál @ 2025-02-19 15:49 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: general pgsql <pgsql-general@lists.postgresql.org>
Laurenz Albe <laurenz.albe@cybertec.at> ezt írta (időpont: 2025. febr.
19., Sze, 12:22):
>
> On Wed, 2025-02-19 at 11:23 +0100, Csányi Pál wrote:
> > I am running postgresql 15 on Debian 12 operating system.
> > I also installed PgAdmin4 and am using the desktop version of it.
> > I have created a trigger function and a trigger which will run the
> > following code, see below.
> > Before this trigger would run, I tried this code.
> > When running the code I get the following error message:
> > ERROR: stack depth limit exceeded HINT: Increase the configuration
> > parameter "max_stack_depth" (currently 2048kB), after ensuring the
> > platform's stack depth limit is adequate.
> >
> > Where am I making a mistake, and what can I do to make this code and
> > this trigger run successfully?
>
> You don't show the entire function and the trigger definition,
Hi Laurenz,
the trigger function definition is like:
BEGIN
INSERT INTO
HAVONKENTI_MEGTAKARITASAINK (EZEN_IDOKOZBEN, MEGTAKARITVA_RSD, KELT)
VALUES ((tsrange((
SELECT
KELT
FROM
PUBLIC.JAVAINK_FORGALMA
WHERE
EN_KAPTAM = 'true'
AND AZ_ARU_NEVE = 'nyugdíjam'
ORDER BY
KELT DESC
LIMIT
1
OFFSET
1
),(
SELECT
KELT
FROM
PUBLIC.JAVAINK_FORGALMA
WHERE
EN_KAPTAM = 'true'
AND AZ_ARU_NEVE = 'nyugdíjam'
ORDER BY
KELT DESC
LIMIT
1
OFFSET
0
),'[)')),(SELECT
SUM(EGYSEGAR * ENNYI_EGYSEG)
FROM
PUBLIC.JAVAINK_FORGALMA
WHERE
A_PENZNEM = 'RSD'
AND KELT >= (
SELECT
KELT
FROM
PUBLIC.JAVAINK_FORGALMA
WHERE
EN_KAPTAM = 'true'
AND AZ_ARU_NEVE = 'nyugdíjam'
ORDER BY
KELT DESC
LIMIT
1
OFFSET
1
)
AND KELT < (
SELECT
KELT
FROM
PUBLIC.JAVAINK_FORGALMA
WHERE
EN_KAPTAM = 'true'
AND AZ_ARU_NEVE = 'nyugdíjam'
ORDER BY
KELT DESC
LIMIT
1
OFFSET
0
)), DEFAULT)
;
END;
and the trigger definition is like:
CREATE OR REPLACE TRIGGER uj_nyugdij_beirasakor_kiold
AFTER INSERT
ON public.javaink_forgalma
FOR EACH ROW
EXECUTE FUNCTION public.ket_nyugdij_kozotti_megtakaritasaink();
COMMENT ON TRIGGER uj_nyugdij_beirasakor_kiold ON public.javaink_forgalma
IS 'Új nyugdíj beírásakor kioldja a két nyugdíj közötti
megtakarításaink nevű szerepet.';
> but from the symptoms it is pretty clear that the trigger is
> modifying the table on which it is defined. That will call the
> trigger again and lead to an endless recursion, which leads to
> the observed error.
>
> Yours,
> Laurenz Albe
Indeed I did, the trigger should have been assigned on
javaink_forgalma table, not on havonkenti_megtakaritasaink table.
Actually, this trigger has been assigned on both tables so far, and I
just deleted it from the havonkenti_megtakaritasaink table by using
PgAdmin4.
Now that I only have this trigger assigned on the javaink_forgalma
table, an INSERT command into this table will activate the trigger
function, which will then insert a new row in the
havonkenti_megtakaritasaink table.
However, by copying the code between BEGIN and END from the trigger
function, and inserting it into the Query tool and then running this
code there, it should insert a line in the havonkenti_megtakaritasaink
table.
But here I got the error message mentioned above. Hey! But now this
code snippet ran without any errors! So what happened here?
Thanks for all the advice!
--
Best wishes by Paul Chanyi!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2025-02-19 10:23 ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
2025-02-19 11:22 ` Re: ERROR: stack depth limit exceeded Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:49 ` Re: ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
@ 2025-02-19 16:09 ` Sándor Daku <daku.sandor@gmail.com>
2025-02-19 16:13 ` Re: ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Sándor Daku @ 2025-02-19 16:09 UTC (permalink / raw)
To: csanyipal@gmail.com; +Cc: general pgsql <pgsql-general@lists.postgresql.org>
On Wed, 19 Feb 2025 at 16:49, Csányi Pál <csanyipal@gmail.com> wrote:
> Indeed I did, the trigger should have been assigned on
> javaink_forgalma table, not on havonkenti_megtakaritasaink table.
> Actually, this trigger has been assigned on both tables so far, and I
> just deleted it from the havonkenti_megtakaritasaink table by using
> PgAdmin4.
>
> Now that I only have this trigger assigned on the javaink_forgalma
> table, an INSERT command into this table will activate the trigger
> function, which will then insert a new row in the
> havonkenti_megtakaritasaink table.
>
> However, by copying the code between BEGIN and END from the trigger
> function, and inserting it into the Query tool and then running this
> code there, it should insert a line in the havonkenti_megtakaritasaink
> table.
>
> But here I got the error message mentioned above. Hey! But now this
> code snippet ran without any errors! So what happened here?
>
> Thanks for all the advice!
>
> --
> Best wishes by Paul Chanyi!
>
>
Hi Pál,
I'm not sure that I follow the timeline correctly, but if the code snippet
works now after deleting the trigger on havonkenti_megtakaritasaink then
nothing to see here.
Before that the trigger activating on insert into
havonkenti_megtakaritasaink inserted a record into
havonkenti_megtakaritasaink which activated the trigger again and this
continued until it ran into the max_stack_depth wall.
Regards,
Sándor
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2025-02-19 10:23 ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
2025-02-19 11:22 ` Re: ERROR: stack depth limit exceeded Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:49 ` Re: ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
2025-02-19 16:09 ` Re: ERROR: stack depth limit exceeded Sándor Daku <daku.sandor@gmail.com>
@ 2025-02-19 16:13 ` Csányi Pál <csanyipal@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Csányi Pál @ 2025-02-19 16:13 UTC (permalink / raw)
To: Sándor Daku <daku.sandor@gmail.com>; +Cc: general pgsql <pgsql-general@lists.postgresql.org>
Sándor Daku <daku.sandor@gmail.com> ezt írta (időpont: 2025. febr.
19., Sze, 17:09):
>
>
>
> On Wed, 19 Feb 2025 at 16:49, Csányi Pál <csanyipal@gmail.com> wrote:
>>
>> Indeed I did, the trigger should have been assigned on
>> javaink_forgalma table, not on havonkenti_megtakaritasaink table.
>> Actually, this trigger has been assigned on both tables so far, and I
>> just deleted it from the havonkenti_megtakaritasaink table by using
>> PgAdmin4.
>>
>> Now that I only have this trigger assigned on the javaink_forgalma
>> table, an INSERT command into this table will activate the trigger
>> function, which will then insert a new row in the
>> havonkenti_megtakaritasaink table.
>>
>> However, by copying the code between BEGIN and END from the trigger
>> function, and inserting it into the Query tool and then running this
>> code there, it should insert a line in the havonkenti_megtakaritasaink
>> table.
>>
>> But here I got the error message mentioned above. Hey! But now this
>> code snippet ran without any errors! So what happened here?
>>
>> Thanks for all the advice!
>>
>> --
>> Best wishes by Paul Chanyi!
>>
>
> Hi Pál,
>
> I'm not sure that I follow the timeline correctly, but if the code snippet works now after deleting the trigger on havonkenti_megtakaritasaink then nothing to see here.
> Before that the trigger activating on insert into havonkenti_megtakaritasaink inserted a record into havonkenti_megtakaritasaink which activated the trigger again and this continued until it ran into the max_stack_depth wall.
>
> Regards,
> Sándor
Hi Sándor,
Thank you very much to you and everyone else who helped me solve this problem!
--
Best wishes by Paul Chanyi!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2025-02-19 10:23 ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
2025-02-19 11:22 ` Re: ERROR: stack depth limit exceeded Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:49 ` Re: ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
@ 2025-02-20 09:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2025-02-20 09:33 UTC (permalink / raw)
To: csanyipal@gmail.com; +Cc: general pgsql <pgsql-general@lists.postgresql.org>
On Wed, 2025-02-19 at 16:49 +0100, Csányi Pál wrote:
> the trigger function definition is like:
> [...]
> INSERT INTO
> HAVONKENTI_MEGTAKARITASAINK (EZEN_IDOKOZBEN, MEGTAKARITVA_RSD, KELT)
> [...]
>
> and the trigger definition is like:
> CREATE OR REPLACE TRIGGER uj_nyugdij_beirasakor_kiold
> AFTER INSERT
> ON public.javaink_forgalma
> FOR EACH ROW
> EXECUTE FUNCTION public.ket_nyugdij_kozotti_megtakaritasaink();
That does not look circular right away, but perhaps there is
a trigger on HAVONKENTI_MEGTAKARITASAINK, or perhaps there is
some other trigger on javaink_forgalma.
It must be something of that kind.
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: ERROR: stack depth limit exceeded
2025-02-19 10:23 ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
@ 2025-02-19 14:31 ` Greg Sabino Mullane <htamfids@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Greg Sabino Mullane @ 2025-02-19 14:31 UTC (permalink / raw)
To: csanyipal@gmail.com; +Cc: general pgsql <pgsql-general@lists.postgresql.org>
Not related to the main question, but that query could use a little
adjustment. Something like:
WITH x AS (
select kelt from javaink_forgalma
where en_kaptam is true and az_aru_neve = 'nyugdíjam'
order by kelt desc limit 2
)
,y AS (select min(kelt) from x)
,z AS (select max(kelt) from x)
INSERT INTO public.havonkenti_megtakaritasaink (ezen_idokozben,
megtakaritva_rsd, kelt)
VALUES (
(select tsrange(min,max,'[)') FROM y,z)
, (select sum(egysegar * ennyi_egyseg) from javaink_forgalma, y, z
where a_penzem = 'RSD' and kelt >= min and kelt < max)
, DEFAULT
);
Also, it's best to use "timestamp with time zone" NOT "timestamp without
time zone" whenever possible (i.e. timestamptz not timestamp)
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-02-20 09:33 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-07 07:45 ERROR: stack depth limit exceeded gzh <gzhcoder@126.com>
2023-09-07 13:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-08 03:32 ` gzh <gzhcoder@126.com>
2023-09-08 06:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-08 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-11 04:57 ` gzh <gzhcoder@126.com>
2023-09-07 13:46 ` Geoff Winkless <pgsqladmin@geoff.dj>
2025-02-19 10:23 ERROR: stack depth limit exceeded Csányi Pál <csanyipal@gmail.com>
2025-02-19 11:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:49 ` Csányi Pál <csanyipal@gmail.com>
2025-02-19 16:09 ` Sándor Daku <daku.sandor@gmail.com>
2025-02-19 16:13 ` Csányi Pál <csanyipal@gmail.com>
2025-02-20 09:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 14:31 ` Greg Sabino Mullane <htamfids@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox