Re: ERROR: stack depth limit exceeded

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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!