Upsert and Before Insert/Update Trigger

Upsert feature in PostgreSQL drove me to shorten SQL statement in my user defined functions. But somehow I overlook PostgreSQL version 10 documentation explaining trigger behavior:

“…If an INSERT contains an ON CONFLICT DO UPDATE clause, it is possible that the effects of row-level BEFORE INSERT triggers and row-level BEFORE UPDATE triggers can both be applied in a way that is apparent from the final state of the updated row, if an EXCLUDED column is referenced…”

For example, I have 2 tables:

CREATE TABLE public.sample
(
      id integer NOT NULL,
      code text NOT NULL,
      CONSTRAINT “sample: id must be unique” PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE public.audit
(
      id integer NOT NULL,
      codechanges integer NOT NULL DEFAULT 0,
      CONSTRAINT “audit: id must be unique” PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

Any record inserted into public.sample will be inserted into public.audit with codechanges 0. Any code changes to existing record in public.sample will increment codechanges in public.audit. For any reason, I implement the behavior in trigger:

CREATE OR REPLACE FUNCTION public.trig_sample() RETURNS trigger AS
$BODY$
BEGIN
      IF TG_WHEN=’BEFORE’ THEN
            IF TG_OP=’INSERT’ THEN
                  INSERT INTO audit (id) VALUES (NEW.id);
            ELSIF TG_OP=’UPDATE’ AND OLD.code IS DISTINCT FROM NEW.code THEN
                  UPDATE audit SET codechanges=codechanges+1 WHERE id = OLD.id;
            END IF;
      END IF;
      RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_sample_before BEFORE INSERT OR UPDATE ON public.sample FOR EACH ROW EXECUTE PROCEDURE public.trig_sample();

And a user defined function to encapsulate the complexity:

Old Style Update/Insert

CREATE OR REPLACE FUNCTION public.sample_update(IN i_id integer, IN t_code text)
RETURNS void AS
$BODY$
BEGIN
      UPDATE public.sample SET code = t_code WHERE id = i_id;
      IF NOT FOUND THEN
            INSERT INTO public.sample (id, code) VALUES (i_id, t_code);
      END IF;
      RETURN;
END;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.sample_update(integer, text) TO public;

Let us give it a try:
SELECT public.sample_update(1, ‘TEST-1’);
SELECT * FROM public.sample;
      id      code
      1      “TEST-1”
SELECT * FROM public.audit;
      id      codechanges
      1      0

SELECT public.sample_update(1, ‘TEST-2’);
SELECT * FROM public.sample;
      id      code
      1      “TEST-2”
SELECT * FROM public.audit;
      id      codechanges
      1      1

Go Shorter with Upsert Version

Now, I modify user defined function with Upsert feature:

CREATE OR REPLACE FUNCTION public.sample_update(IN i_id integer, IN t_code text) RETURNS void AS
$BODY$
      INSERT INTO public.sample (id, code) VALUES ($1, $2)
      ON CONFLICT (id) DO UPDATE SET code = EXCLUDED.code;
$BODY$ LANGUAGE sql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.sample_update(integer, text) TO public;

And repeat the test:
SELECT public.sample_update(1, ‘TEST-3’);

But PostgreSQL complains:

ERROR: duplicate key value violates unique constraint “audit: id must be unique”
DETAIL: Key (id)=(1) already exists.
CONTEXT: SQL statement “INSERT INTO audit (id) VALUES (NEW.id)”
PL/pgSQL function trig_sample() line 5 at SQL statement
SQL function “sample_update” statement 1

I realize that I overlook the Overview of Trigger Behavior clause in PostgreSQL documentation. So, I modify my trigger, move the logic from BEFORE to AFTER:

CREATE OR REPLACE FUNCTION public.trig_sample() RETURNS trigger AS
$BODY$
BEGIN
      /*IF TG_WHEN=’BEFORE’ THEN*/
      IF TG_WHEN=’AFTER’ THEN
            IF TG_OP=’INSERT’ THEN
                  INSERT INTO audit (id) VALUES (NEW.id);
            ELSIF TG_OP=’UPDATE’ AND OLD.code IS DISTINCT FROM NEW.code THEN
                  UPDATE audit SET codechanges=codechanges+1 WHERE id = OLD.id;
            END IF;
      END IF;
      RETURN NEW;
END$BODY$ LANGUAGE plpgsql VOLATILE;

DROP TRIGGER trig_sample_before ON public.sample;

CREATE TRIGGER trig_sample_after AFTER INSERT OR UPDATE ON public.sample FOR EACH ROW EXECUTE PROCEDURE public.trig_sample();

Try the test once more:
SELECT public.sample_update(1, ‘TEST-3’);

And no more complains:
SELECT * FROM public.sample;
      id      code
      1      “TEST-3”
SELECT * FROM public.audit;
      id      codechanges
      1      2

A lesson learned.

Advertisements
1 comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: