Thread: Trigger function

Hi,

I have a set of tables, ‘object’, ‘obtype’, ‘host’ and 
domain:

CREATE TABLE "domain"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000001 INCREMENT 
BY 1),
"name" VARCHAR(64) NOT NULL,
CONSTRAINT "domain__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "obtype"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 2800001 INCREMENT 
BY 1),
"name" VARCHAR(16) NOT NULL,
CONSTRAINT "obtype__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "host"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 2900001 INCREMENT 
BY 1),
"name" VARCHAR(32) NOT NULL,
"domain" INTEGER NOT NULL,
"object" INTEGER NOT NULL,
CONSTRAINT "host__id__pk" PRIMARY KEY ("id")
);

CREATE TABLE "object"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 3200001 INCREMENT 
BY 1),
"type" INTEGER NOT NULL,
CONSTRAINT "object__id__pk" PRIMARY KEY ("id")
);

ALTER TABLE "domain" ADD CONSTRAINT "domain__name__uk" UNIQUE ("name");

ALTER TABLE "obtype" ADD CONSTRAINT "obtype__name__uk" UNIQUE ("name");

ALTER TABLE "host" ADD CONSTRAINT "host__name_domain__uk" UNIQUE 
("name","domain");

ALTER TABLE "host" ADD CONSTRAINT "host__object__uk" UNIQUE ("object");

ALTER TABLE "host" ADD CONSTRAINT "host__object__fk" FOREIGN KEY 
("object") REFERENCES "object" ("id") ON DELETE CASCADE;

ALTER TABLE "host" ADD CONSTRAINT "host__domain__fk" FOREIGN KEY 
("domain") REFERENCES "domain" ("id") ON DELETE RESTRICT;

ALTER TABLE "object" ADD CONSTRAINT "object__type__fk" FOREIGN KEY 
("type") REFERENCES "obtype" ("id") ON DELETE RESTRICT;

I am looking at whether functions can help me automate creating a new 
object when a new host is added.

I can do this manually with the following code:

WITH object_id AS
(INSERT INTO public.object (type)
VALUES (
    ( SELECT obtype.id
       FROM public.obtype
       WHERE obtype.name LIKE 'host'
       )
)
RETURNING id)
INSERT INTO host (name, domain, object)
VALUES ('gary', 1000001, (SELECT * FROM object_id));

I have a number of questions but I would like to begin by asking whether 
this a candidate for a trigger function on table ‘host’, triggered 
before an insert?

Many thanks,

NicM

On Mon, Jul 26, 2021 at 03:17:12PM +0000, Nicolas Mitchell wrote:
> I am looking at whether functions can help me automate creating a new object
> when a new host is added.
> 
> I can do this manually with the following code:
> 
> WITH object_id AS
> (INSERT INTO public.object (type)
> VALUES (
>     ( SELECT obtype.id
>       FROM public.obtype
>       WHERE obtype.name LIKE 'host'
>       )
> )
> RETURNING id)
> INSERT INTO host (name, domain, object)
> VALUES ('gary', 1000001, (SELECT * FROM object_id));
> 
> I have a number of questions but I would like to begin by asking whether
> this a candidate for a trigger function on table ‘host’, triggered before an
> insert?

*after* insert, not *before*.
But other than that yes.

If I might suggest:
https://www.depesz.com/2012/11/14/how-i-learned-to-stop-worrying-and-love-the-triggers/

depesz

On 27 Jul 2021, at 7:38, hubert depesz lubaczewski wrote:

> *after* insert, not *before*.
> But other than that yes.

Apologies for the lengthy reply; I’ve done a lot of thinking today.

I cannot understand why it would be *after* insert: A required column in
a new host row (host.object) cannot be populated until a new row is
inserted into the object table. Therefore the function must (surely?) be
executed prior to an insert (on the host table).

 From the point of view of a user, the command they would issue is:

=> INSERT INTO host (name, domain) VALUES ('gary', 1000001);

I imagined the trigger/function taking the supplied information (name,
domain) and retrieving the value for host.object before insert.

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
   WITH object_id AS
(INSERT INTO public.object (type)
VALUES (
    ( SELECT obtype.id
       FROM public.obtype
       WHERE obtype.name LIKE 'host'
       )
)
RETURNING id)
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();

Later, and this is for another day, after reading your article (which
mentions view triggers) it occurred to me to try a trigger attached to a
view of the hosts table. I created a view and attached a trigger (only
INSTEAD OF allowed, noted) calling the same function:

CREATE VIEW public.v_host
(
   id,
   "name",
   "domain",
   "object"
)
AS
SELECT
   host.id,
   host.name,
   host.domain,
   host.object
FROM host;

CREATE TRIGGER trig__v_host__bi
   INSTEAD OF INSERT
   ON public.v_host
   FOR EACH ROW
   EXECUTE PROCEDURE public.func__host__bi();

CREATE OR REPLACE FUNCTION public.func__host__bi()
RETURNS trigger AS
$$
begin
   WITH object_id AS
(INSERT INTO public.object (type)
VALUES (
    ( SELECT obtype.id
       FROM public.obtype
       WHERE obtype.name LIKE 'host'
       )
)
RETURNING id)
INSERT INTO host (name, domain, object)
VALUES (NEW.name, NEW.domain, (SELECT * FROM object_id));

RETURN NEW;
end
$$
LANGUAGE 'plpgsql'

The previous psql command would now be:

=> INSERT INTO v__host (name, domain) VALUES ('gary', 1000001);

Which succeeds when executed,

- inserting a new object (of type host)
- inserting a new host, with the object.id (host.object) of the newly
created object
- incrementing the two sequences by one each

> If I might suggest:
> https://www.depesz.com/2012/11/14/how-i-learned-to-stop-worrying-and-love-the-triggers/

Thank you, this was really helpful. I expect to be referring back to it
in the future.

Many thanks,

Nic

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();

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

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

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

> On Tuesday, July 27, 2021, David G. Johnston <david.g.johnston@gmail.com>
> wrote:
>
>>
>> When you write a trigger for a table you should be executing commands
>> against the same table.
>>
>
> Should [NOT] be executing [data changing] commands against the same table.

Crumbs, but thank you. Digesting now.

Nic

On 28 Jul 2021, at 0:19, David G. Johnston wrote:

> On Tue, Jul 27, 2021 at 4:04 PM Nicolas Mitchell 
> <mitchelln@posteo.net>
> wrote:
>
>>
>>> 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.
>>
>>

I worked out a solution to my question (as stated, but with a different 
table):

create function func__new_user_object() returns trigger
     language plpgsql
as
$$
begin
if NEW.object is NULL then

-- RAISE NOTICE 'No user.object value, generating a new user object';

    WITH object_id AS (
    INSERT INTO public.object (type)
    VALUES (
    (
    SELECT obtype.id
    FROM public.obtype
    WHERE obtype.name LIKE 'user'
    ))
    RETURNING id)
     SELECT * FROM object_id INTO NEW.object;

end if;
RETURN NEW;
end
$$;

CREATE TRIGGER trig__new_user_object
   BEFORE INSERT
   ON public."user"
   FOR EACH ROW
   EXECUTE PROCEDURE public.func__new_user_object();

>
> Personally, I'm generally against placing this kind of data 
> construction
> logic inside triggers.

I have been working with a mind to keep as much logic as I can inside 
the database/PostgreSQL. I can’t tell from your comment whether you 
prefer other mechanisms available within PG to achieve the same, or 
prefer to manage these operations in an application. If within PG, then 
I’d view that as something I should explore. Otherwise, I’m not 
keen, at present, to push things into an application when they can be 
achieved within PG - this being my own (fairly uneducated) preference. 
As you may have gathered, this is a new area for me and I am interested 
to hear opinions - just a few pointers - now I have what seems to be a 
working solution to my question.

Nic

On 28 Jul 2021, at 1:46, David G. Johnston wrote:

> I'd probably do "CREATE PROCEDURE create_new_host(new_host_name text,
> new_host_domain integer) ... SECURITY DEFINER"
>
> Then grant permission to call that procedure to roles that need to create
> new host records.  Your original CTE would then be executed within the
> procedure.  Roles would not be given permission to insert directly into the
> host or related tables - but the owner of the procedure would.
>

Okay, that’s something I can get my teeth into.

Many thanks,

Nic