Preserving data after updates

[Berend Tober <btober@xxxxxxxxxxxxxxxx>]

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings