Re: forcing a literal value in a column - Mailing list pgsql-general

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