Preserving data after updates - Mailing list pgsql-general

A few months ago, a question by Scott Frankel produced a suggestion from
Greg Patnude which I found very exciting that had to do with using pg
table inheritance to maintain an audit or row change history table. I've
been testing Patnude's idea and ran into a problem, described below, and
wanted to ask about work-around suggestions.

Testing had so far entailed occasionally dumping the production data
base, restoring to DEV, and then modifying DEV to include several
"history" tables, using a script similar to that which I documented on
the PG web site. So today, I tried for the first time dumping DEV after
making the history table additions and then testing the restore from the
dump script so produced. The restore failed.

The problem is that one of my parent tables has table constraints:

CREATE TABLE person
(
  person_pk int4 NOT NULL DEFAULT nextval('"person_person_pk_seq"'::text),
  last_name varchar(24),
  first_name varchar(24),
  middle_name varchar(24),
  e_mail_address name,
  social_security_no varchar(11),
  CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
  CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
  CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
)
WITHOUT OIDS;

I create the history table with

CREATE TABLE person_change_history(
    action VARCHAR(6),
    update_date TIMESTAMP NOT NULL DEFAULT NOW(),
    update_user NAME NOT NULL DEFAULT CURRENT_USER
    ) INHERITS (person) WITHOUT OIDS;

CREATE RULE person_ru AS ON UPDATE TO person
    DO INSERT INTO person_change_history
    SELECT *, 'UPDATE' FROM ONLY person WHERE person_pk = old.person_pk;

CREATE RULE person_rd AS ON DELETE TO person
    DO INSERT INTO person_change_history
    SELECT *, 'DELETE' FROM ONLY person WHERE person_pk = old.person_pk;

But after doing a dump of  the modified data base, the script created by
pg dump wants to recreate the history table as

CREATE TABLE person_change_history
(
  person_pk int4 NOT NULL DEFAULT nextval('"person_person_pk_seq"'::text),
  last_name varchar(24),
  first_name varchar(24),
  middle_name varchar(24),
  e_mail_address name,
  social_security_no varchar(11),
  "action" varchar(6),
  update_date timestamp NOT NULL DEFAULT now(),
  update_user name NOT NULL DEFAULT "current_user"(),
  CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
  CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
  CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
) INHERITS (person)
WITHOUT OIDS;

When I run the script to restore the dumped, modified, data base, psql
raises an error when creating the history table because the table
constraints already exist"

psql:paid-5434.sql:7678: ERROR:  constraint "person_e_mail_address"
already exists for relation "person_change_history"

Any suggestion on how to get around this problem?

I don't want to have to manually modified the pg_dump output script so
as to delete the constraint definitions from the history table
definition, because that sort of manual intervention really gets in the
way of good administrative procedures for disaster recovery if this
scheme were to be implemented in the production data base.

-- BMT