Re: [HACKERS] Re: trouble creating log table with rules

I said:

> > 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 and new.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 NULL and new.serial_num_cpu IS NULL) or
> >       (old.serial_num_cpu IS NULL and new.serial_num_cpu IS NOT 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.
> >

wieck>     You didn't tell us which version of PostgreSQL and (more
wieck> important) if the error occurs during CREATE RULE or when updating
wieck> machine.

Duhhh. sorry!
postgresql 6.5; FreeBSD 3.2 stable - recent.

The error occurs during CREATE RULE.

wieck> So you could setup  single  action  rules  per field to get (mostly)
wieck> the same results. 

I previously had tried to do the same thing with many (more than 10) distinct 
single-action rules (sorry, don't have the exact syntax of what I used.. but
it was very similar to this example.).

In that case, the CREATE RULE worked properly, but at update time it bombed
out (again, don't have the detail anymore).  The error message indicated that 
it thought there was a loop in my rules, something about "more than 10"... 
In that case, as I remember, the backend did not crash -- it just declined to 
execute the update.

I'll try multiple multi-action rules to see if I can do what I want..

Is this (the way I'm writing the rules) the best approach?


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