Infrastructure at your Service

Franck Pachot

Pass a variable to a trigger in PostgreSQL

By April 22, 2021 Postgres 2 Comments

By Franck Pachot

.
With Oracle there are many ways to set a state in the session: context variables, set with DBMS_SESSION.SET_CONTEXT and retrieved with SYS_CONTEXT, or package global variables, or global or private temporary tables with ON COMMIT PRESERVE ROWS,…

How would you do it in PostgreSQL? I’m taking an example from a Yugabyte slack question, as Yugabyte SQL layer is the PostgreSQL one.


CREATE TABLE employees (
  employee_no integer PRIMARY KEY,
  name text,
  department text
);

INSERT INTO employees (employee_no, name, department) VALUES 
(1221, 'John Smith', 'Marketing'),
(1222, 'Bette Davis', 'Sales'),
(1223, 'Lucille Ball', 'Operations'),
(1224, 'John Zimmerman', 'Sales');

CREATE TABLE mgr_table (
  mgr_id integer references employees,
  employee_no integer references employees,
  primary key(mgr_id, employee_no)
);

insert into mgr_table values(1223,1222);

We have an employee/manager table example:


CREATE TABLE employee_dept_changes (
  employee_no integer NOT NULL references employees,
  name text,
  department text,
  changed_on TIMESTAMP(6) NOT NULL,
  changed_by integer
);

This table is there to log the changes when an employee is transferred to another manager. You can see that we log the values, but also the context about who is transferring because this operation is allowed only by a manager.

Ideally, all database operations are encapsulated in a microservice. The application calls a stored procedure that does all the logic: update the manager and log the change. And this procedure has all context. However, the idea here is to do this logging through a trigger. The trigger has access to the table values, but how can we pass the context of who is doing this change? Without context variables, or package variables, this requires something else.

If you look at the stackoverflow for this question, you will see Frits Hoogland and Bryn Llewellyn answers with workaround and recommendations. I’m just adding here a possibility (with the same recommendation: don’t have procedures calling SQL calling triggers, but all logic encapsulated in procedural code calling SQL).


ALTER TABLE employees ADD COLUMN
  "_trigger_state_mgr_id" integer --> this is added to pass the state variable

I’m adding a column to my table. This will not take any storage but will be used only to pass some values to the trigger


CREATE OR REPLACE FUNCTION record_dept_changes()
RETURNS TRIGGER AS
$$
BEGIN
 IF NEW.department  OLD.department
  THEN INSERT INTO employee_dept_changes(employee_no, name, department, changed_on, changed_by)
   VALUES(OLD.employee_no, OLD.name, OLD.department, now(), NEW."_trigger_state_mgr_id"  --> passing the state to the added column
   );
 END IF;
 NEW."_trigger_state_mgr_id" := null; --> setting null not to persist anything (or maybe a real "last_update_by" column makes sense?)
 RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

The trigger logs the change into the “changes” table, reading this added column value. But I set this value to null once used because I don’t want to waste any storage (disk and memory) for it.


CREATE TRIGGER dept_changes
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE PROCEDURE record_dept_changes();

In PostgreSQL the trigger calls the procedure which has access to the NEW. valies


CREATE OR REPLACE PROCEDURE transfer_employee(integer, integer, text)
LANGUAGE plpgsql
AS $$
BEGIN
  -- IF employee reports to mgr, allow mgr to transfer person
  IF EXISTS (SELECT employee_no FROM mgr_table where mgr_id = $1 and employee_no = $2)
  THEN
   UPDATE employees
   SET department = $3, "_trigger_state_mgr_id" = $1 --> passing the state through the column
   WHERE employee_no = $2;
   COMMIT;
  END IF;
END;
$$;

Here is my procedure that implements the transfer service. In addition to checking if the manager is allowed to do the transfer, and doing the update, I set the additional column with the context value.

This column is declared in the metadata, is used by the update statement but is discarded by the trigger. If PostgreSQL had a feature like the Oracle invisible column, I would set this column as invisible as I don’t want to see it in SELECT * or INSERT to it. I can do the same with a view on top of the table.

In summary:

  • you may have less feature in open source software than commercial ones, but this probably means that there’s something to do differently in the application design. Commercial vendors can implement anything when important customers pay for it. An open source community will not add more complexity to workaround a problem that should be addressed by the application design. Don’t call SQL DML with trigger magic behind. Call a procedure that has all the logic, including all DML (main change and audit) and verifications
  • workarounds are valid as long as you document them (what they do, and why) and verify all side effects (like setting it to null afterwards). There are often many workarounds and choose the one that makes more sense. Here, maybe the “_trigger_state_mgr_id” will become a real column to persist one day, if there’s a need to store the latest manager that assigned the employee to a department

2 Comments

  • This is terribly dirty. When you need to pass a parameters to triggers, then you use triggers badly. When you need to pass a parameters, then you should to use function, not an trigger. Here should be very strong warning. DON’T DO THIS!

  • Hi Pavel, yes, I agree that this use of trigger is not correct. Better do all in a procedure that calls the DML rather than behind it. Or use CDC to do audit log behind the statements. But sometimes people have limited possibilities, and workarounds are valid if it is for short time and with all consequences well understood. Independently of the bad design and possible future side effects because using features not used for this goal, which problem can you see with this?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Data Hero
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod