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