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

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!

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.

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!

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_megtakaritasainkthen nothing to see here. 
> Before that the trigger activating on insert into havonkenti_megtakaritasaink inserted a record into
havonkenti_megtakaritasainkwhich 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!

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.