Hello,
we are using a kind of auditing like describes in the manual:
http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html
But instead of the example we are using a structure like:
CREATE TABLE emp (
empname text NOT NULL,
stamp timestamp NOT NULL,
userid integer NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
increment_me bigserial,
audit_stamp timestamp NOT NULL,
audit_userid integer NOT NULL,
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid intger NOT NULL,
empname text NOT NULL,
salary integer
);
When there is an insert in emp, our after-trigger will set in the emp_audit:
audit_stamp = new.stamp
audit_userid = new.userid
stamp = new.stamp
userid = new.userid
Operation = 'I'
When there is a update:
audit_stamp = new.stamp
audit_userid = new.userid
stamp = old.stamp
userid = old.userid
Operation = 'U'
When there is a delete:
audit_stamp = ?
audit_userid = ?
stamp = old.stamp
userid = old.userid
Operation = 'D'
So we are always logging the old row and the userid from the person that was
making the update/insert/delete.
That will work well for insert and an update because we are submitting the
userid and stamp values.
But on a delete. What should we do?
The userid is a number that came from our application, so we need to set it
before a delete statement will be fired.
But how can we do that?
Is there a way to just make a query like:
set my_userid = 1234
And then make the delete command?
How would you solve this problem, maybe our logging design is not good?
We also need that we set such a variable (if it is possible), that it is not
available for the other connections.
I think that we have to turn off connection pooling and connect and
disconnect on every query so there will be no influence from other threads
that are using different my_userid?
What should we do?
Thank you
Christian
