We have an application that we're trying to get audit logging in place for … right now, we're doing it as a stored procedure that is called by the java app for each time it does an update / insert / delete, but I'd like to move it to a trigger … the problem is (or so we think) is that we're using a persistent connection vs re-establishing a connection …
I don't know the internals of this … if two ppl log into the system (same db role … our auth system is based on a users table), and both were to do a transaction to do an update of a table:
user 1 user 2
begin;
begin;
set variable = 1;
set variable = 2;
update table using variable;
end;
end;
now, since they are both in a transaction, does the connection pool start up one connection per user, or does it somehow 'tag' the queries and re-use the same connection?
What if I did something foolish like avoid the begin/end around the transaction? so user 1 sends his set, then user 2 sends his set, then user 1 tries to update based on the variable? Do the queries get sent across the same connection, and user 2's setting of variable overrides user 1?
I've already tested using psql that if I do:
openstudent=> begin;
BEGIN
openstudent=> set var.modified_by = 1;
SET
openstudent=> end;
COMMIT
openstudent=> show var.modified_by;
var.modified_by
-----------------
1
(1 row)
that variable retains the '1' value … so the end doesn't revert the variable to an unset state …
What I'm trying to do is to get the modified_by id passed down into a trigger, instead of using a stored procedure … current_user doesn't apply, since the login isn't at the 'db role' level … but I need to make sure how it works with the connection pooling, whether what I'm thinking is even possible / reliable …
Thanks ...