Re: forcing a literal value in a column

Hello all,

in my audited tables I do this:
modify_when time stamp with time zone not null default CURRENT_TIMESTAMP check(modify_when=now()),

This works on insert. However, on update a function runs
via a trigger but fails with:
ExecReplace: rejected due to CHECK constraint audit_mark_modify_when

I can't get my head around why. The definitions go like this:

---------------------
1) an audited table (all but "dummy" inherited
from table audit_mark)

Table "test"
Attribute | Type | Modifier
-------------+--------------------------+-------------------------------------------------------------
pk_audit | integer | not null default nextval('"audit_mark_pk_audit_seq"'::text)
row_version | integer | default 0
modify_when | timestamp with time zone | not null default "timestamp"('now'::text)
modify_by | name | not null default "current_user"()
dummy | character varying(10) |
Constraints: (modify_by = "current_user"())
(modify_when = now())
------------------
2) the corresponding audit trail table (all but "dummy"
inherited from table audit_log):

Table "log_test"
Attribute | Type | Modifier
---------------+--------------------------+------------------------------------------------------------
pk_audit | integer | not null default nextval('"audit_log_pk_audit_seq"'::text)
orig_version | integer | not null default 0
orig_when | timestamp with time zone | not null
orig_by | name | not null
orig_tableoid | oid | not null
audit_action | character varying(6) | not null
audit_when | timestamp with time zone | not null default "timestamp"('now'::text)
audit_by | name | not null default "current_user"()
dummy | character varying(10) |
Constraints: (audit_by = "current_user"())
(audit_when = now())
((audit_action = 'UPDATE'::"varchar") OR (audit_action = 'DELETE'::"varchar"))
------------------
3) the function and trigger used to keep the audit trail:

CREATE FUNCTION f_audit_test() RETURNS OPAQUE AS '
BEGIN
-- explicitely increment row version counter
NEW.row_version := OLD.row_version + 1;
INSERT INTO log_test (
-- auditing metadata
orig_version, orig_when, orig_by, orig_tableoid, audit_action,
-- table content, except audit_mark data
dummy
) VALUES (
-- auditing metadata
OLD.row_version, OLD.modify_when, OLD.modify_by, TG_RELID, TG_OP,
-- table content, except audit_mark data
OLD.dummy
);
return NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER t_audit_test
BEFORE UPDATE OR DELETE
ON test
FOR EACH ROW EXECUTE PROCEDURE f_audit_test();
---------------------
Insert works, update fails. Delete, too, but that's due to my
returning NEW which isn't defined, so don't mind that.

Any help is appreciated. This is on 7.1.3 (I know that's
rather old).

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346