I would like to record the id of a user in the session/transaction, using
SET, so I could be able to access it later in a trigger function, using
current_setting. Basically, I’m trying option n2 from a very similar ticket posted previously, with the difference that I’m using PG 10.1 .
I’ve been trying 3 approaches to setting the variable:
SET local myvars.user_id = 4, thereby setting it locally in the transaction;
SET myvars.user_id = 4, thereby setting it in the session;
SELECT set_config('myvars.user_id', '4', false), which depending of the last argument, will be a shortcut for the previous 2 options.
None of them is usable in the trigger, which receives
NULL when getting the variable through
current_setting. Here is a script I’ve devised to troubleshoot it (can be easily used with the postgres docker image):
database=$POSTGRES_DB user=$POSTGRES_USER [ -z "$user" ] && user="postgres" psql -v ON_ERROR_STOP=1 --username "$user" $database <<-EOSQL DROP TRIGGER IF EXISTS add_transition1 ON houses; CREATE TABLE IF NOT EXISTS houses ( id SERIAL NOT NULL, name VARCHAR(80), created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(), PRIMARY KEY(id) ); CREATE TABLE IF NOT EXISTS transitions1 ( id SERIAL NOT NULL, house_id INTEGER, user_id INTEGER, created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(), PRIMARY KEY(id), FOREIGN KEY(house_id) REFERENCES houses (id) ON DELETE CASCADE ); CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS $$ DECLARE user_id integer; BEGIN user_id := current_setting('myvars.user_id')::integer || NULL; INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id); RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER add_transition1 AFTER INSERT OR UPDATE ON houses FOR EACH ROW EXECUTE PROCEDURE add_transition1(); BEGIN; %1% SELECT current_setting('myvars.user_id'); %2% SELECT set_config('myvars.user_id', '55', false); %3% SELECT current_setting('myvars.user_id'); INSERT INTO houses (name) VALUES ('HOUSE PARTY') RETURNING houses.id; SELECT * from houses; SELECT * from transitions1; COMMIT; DROP TRIGGER IF EXISTS add_transition1 ON houses; DROP FUNCTION IF EXISTS add_transition1; DROP TABLE transitions1; DROP TABLE houses; EOSQL
The conclusion I came to was that the function is triggered in a different transaction and a different (?) session. Is this something that one can configure, so that all happens within the same context?