Subject: Trigger function

From: "Nicolas Mitchell" <[email-masked]>
Date: Mon, 26 Jul 2021 15:17:12 +0000

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

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Tue, 27 Jul 2021 08:38:05 +0200

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.
> 
> 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

---

From: "Nicolas Mitchell" <[email-masked]>
Date: Tue, 27 Jul 2021 21:05:26 +0000

> *after* insert, not *before*.

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

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 27 Jul 2021 15:13:22 -0700

On Tuesday, July 27, 2021, Nicolas Mitchell <[email-masked]> wrote:

> (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.
>

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…

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

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?).

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 27 Jul 2021 15:15:16 -0700

> When you write a trigger for a table you should be executing commands

Should [NOT] be executing [data changing] commands against the same table.

David J.

---

From: "Nicolas Mitchell" <[email-masked]>
Date: Tue, 27 Jul 2021 23:04:03 +0000

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

>> (object,

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

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

> the trigger function (i.e., modify your “return new;” line - or 

I’m absorbing this sentence…

Many thanks for your assistance.

Nic

---

From: "Nicolas Mitchell" <[email-masked]>
Date: Tue, 27 Jul 2021 23:06:29 +0000

> Should [NOT] be executing [data changing] commands against the same table.

Crumbs, but thank you. Digesting now.

Nic

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 27 Jul 2021 16:19:57 -0700

> public.object…) row should reside elsewhere.
Since the trigger is on host but the insert is going to object my
observation does not apply.

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

David J.

---

From: "Nicolas Mitchell" <[email-masked]>
Date: Wed, 28 Jul 2021 00:41:55 +0000

>> 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 

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

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 27 Jul 2021 17:46:54 -0700

On Tue, Jul 27, 2021 at 5:41 PM Nicolas Mitchell <[email-masked]>
wrote:

> If within PG, then
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.

David J.

---

From: "Nicolas Mitchell" <[email-masked]>
Date: Wed, 28 Jul 2021 00:49:48 +0000

> host or related tables - but the owner of the procedure would.

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

Many thanks,

Nic