trouble creating log table with rules - Mailing list pgsql-sql

I'm trying to create a table (machine_log) that logs changes to another table
(machine).  My goal is to track changes to most of the fields, and to also
track inserts and deletes in the machine table.  I want to know who/when/what
for each change.  I've had some success but not total...

The machine table has a gazillion fields in it (it's a legacy thing; we're
stuck with it for the moment) -- it's certainly not very elegant.  Here are
the tables:

CREATE SEQUENCE "machine_machnum_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
CREATE TABLE "machine" ("host" text NOT NULL,"serial_num_cpu" text,"asset_tag_cpu" text,"risc_tag" text,"dept"
text,"date_purch"date DEFAULT '1/1/0000',"mach_state" text,"owner" text,"cube" text,"mach_user" text,"update_id"
text,"date_added"date DEFAULT text 'now',"date_touched" date DEFAULT text 'now',"date_updated" date DEFAULT text
'now',"notes"text,"po_num" text,"project" text,"asset_type" text,"net_intf" text,"ip_addr" text,"net_num" int2,"e_net"
text,"cpu"text,"os_rev" text,"memory" int2,"swap" float4,"b_systype" text,"hostid" text NOT NULL,"sys_type"
text,"disk_total"int4,"n_cpus" int2,"kernel_id" text,"disk_used" int4,"frame_buff" text,"bkup_status" text,"disk_avail"
int4,"machnum"int4 DEFAULT nextval ( '"machine_machnum_seq"' ) NOT NULL,"y2k" text);
 

CREATE TABLE "machine_log" ("who" text,"date" datetime,"machnum" int4, "col" text,  -- which column in machine was
changed"newval"text); -- and it's new value
 
CREATE UNIQUE INDEX "machine_host_key" on "machine" using btree ( "host" "text_ops" );
CREATE UNIQUE INDEX "machine_machnum_key" on "machine" using btree ( "machnum" "int4_ops" );


I've read the docs in the programmers manual, and can create rules like this:

CREATE RULE "m_log_change" AS ON UPDATE TO "machine"
do ( INSERT INTO machine_log (who, date, machnum, col, newval)   SELECT getpgusername(), 'now'::text, old.machnum,
'host',new.host   WHERE (new.host != old.host) or (old.host IS NOT NULL and new.host IS NULL) or    (old.host IS NULL
andnew.host IS NOT NULL);
 
 INSERT INTO machine_log (who, date, machnum, col, newval)   SELECT getpgusername(), 'now'::text, old.machnum,
'serial_num_cpu',new.serial_num_cpu   WHERE (new.serial_num_cpu != old.serial_num_cpu) or (old.serial_num_cpu IS NOT
NULLand new.serial_num_cpu IS NULL) or    (old.serial_num_cpu IS NULL and new.serial_num_cpu IS NOT NULL);
 
);

So far so good, and this works for any update to these particular fields.
(Side question: why do I have to do the tests to see if one of the values is
null?  If I leave these out, the rule doesn't fire when either old or new is
NULL.)

My big problem is that if I replicate this enough times to cover the fields I 
want, I get this error:

pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is impossible.  Terminating.


Is there a way I can avoid this error?  Is there a better way to code these
rules? 

(part 2:)
Is there a halfway elegant way to code a rule that will log inserts/deletes?
(I suppose I can name all the fields in machine and the who/when
fields .. is that the only way?)  

I'd rather use SELECT * from machine, but how can I add my extra two fields
that tell me who/when?


Jim Rowan            DCSI            DCE/DFS/Sysadmin Consulting
jmr@computing.com                                            (512) 374-1143