From: "Nicolas Mitchell" <mitchelln@posteo.net>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "hubert depesz lubaczewski" <depesz@depesz.com>,
 pgsql-novice@lists.postgresql.org
Subject: Re: Trigger function
Date: Tue, 27 Jul 2021 23:04:03 +0000
Message-ID: <53180757-855C-4FFF-B5CC-572BAEFD9397@posteo.net>
In-Reply-To: 
 <CAKFQuwazmQBDYdt+zcw2WV+3bONRjbe7qoHiMnREYj+VRXEXjA@mail.gmail.com>
References: <30B8A3D0-8A87-40E2-BE40-31A17BA4B944@posteo.net>
 <20210727063805.GA28983@depesz.com>
 <2D1DF30A-25BC-48CA-B502-D8B3147B9BEB@posteo.net>
 <CAKFQuwazmQBDYdt+zcw2WV+3bONRjbe7qoHiMnREYj+VRXEXjA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/53180757-855C-4FFF-B5CC-572BAEFD9397%40posteo.net>
Precedence: bulk

On 27 Jul 2021, at 23:13, David G. Johnston wrote:

> On Tuesday, July 27, 2021, Nicolas Mitchell <mitchelln@posteo.net> 
> wrote:
>
>>
>> But when I have tried this with the following trigger/function
>> (BEFORE/AFTER), PG goes into a loop. The two associated sequences 
>> (object,
>> host) are incremented until I break the execution but no insert 
>> happens in
>> either table. My code is causing an endless loop. I’m too green to
>> understand why! I’d be grateful for any hints to help me on my way.
>>
>> CREATE OR REPLACE FUNCTION public.func__host__bi()
>> RETURNS trigger AS
>> $$
>> begin
>>
>> INSERT INTO host (name, domain, object)
>> VALUES (NEW.name, NEW.domain, (SELECT * FROM object_id));
>> RETURN NEW;
>> end
>> $$
>> LANGUAGE 'plpgsql'
>>
>> CREATE TRIGGER trig__host_bi
>>   BEFORE INSERT <————————————> or AFTER 
>> INSERT
>>   ON public."host"
>>   FOR EACH ROW
>>   EXECUTE PROCEDURE public.func__host__bi();
>>
>
> You are getting an infinite cycle because while in the middle of 
> inserting
> a row into host, which provokes the trigger, you go and execute 
> another
> insert command for host, provoking the same trigger, performing yet 
> another
> insert, provoking the same trigger, etc…

I see, I think.

INSERT going to happen > BEFORE INSERT trigger firing function with > 
INSERT INTO host > INSERT going to happen > BEFORE INSERT trigger firing 
function with > etc…

>
> When you write a trigger for a table you should be executing commands
> against the same table.

I believe this implies that the code creating a new object (INSERT INTO 
public.object…) row should reside elsewhere.

>
> You change the data in the ongoing insert by returning a different row 
> from
> the trigger function (i.e., modify your “return new;” line - or 
> modify NEW
> itself?).

I’m absorbing this sentence…

Many thanks for your assistance.

Nic