Re: Updating a table via a view - Mailing list pgsql-general

Glen Eustace <geustace@godzone.net.nz> writes:
> I am trying to maintain a history of rows. The base table has a start
> and end date. I am updating the table using a view and a rule.  The
> rule, updates the end date on the current record and then inserts a new
> row with the modified columns, or at least thats what I want to happen.

> The update is occuring but the insert doesn't.  I get no error but no
> row. The rule looks like this;

> CREATE RULE a_update
>     AS ON UPDATE TO a DO INSTEAD
>         (UPDATE a_hist
>             SET tend = now()
>             WHERE (a.x = old.x)
>               AND (a.tend = 'infinity'::timestamptz);
>          INSERT INTO a_hist (
>             x,
>             tstart,
>             tend,
>             y,
>             z
>          ) VALUES (
>             new.x,
>             now(),
>             'infinity'::timestamptz,
>             new.y,
>             new.z
>          );
>         );

> Any pointer as to what I am doing wrong ?

Is the base table actually a table?  Or is it a view on a_hist?
(Given that you say "DO INSTEAD", I'm suspicious it's a view.)
If it's a view, then the likely problem is that after the first
UPDATE, the view row that the notional UPDATE is for doesn't exist
anymore.  Then the INSERT doesn't fire, because it's really been
rewritten into something along the lines of

INSERT INTO a_hist (...)
  SELECT new.x, now(), 'infinity'::timestamptz, new.y, new.z
  FROM view WHERE view-conditions AND original-update's-conditions

If I'm guessing correctly that the view-conditions include something
about "a.tend < infinity", then this will produce no rows to insert.

I would recommend that you turn "a" into an actual table that actually
stores the current values of x,y,z.  Then the rule would become DO
instead of DO INSTEAD, but it should work the way you expect.
(You'd also have the option of using a trigger instead of a rule to
update the history table, which might be better for performance.)

BTW, another common gotcha with rules is that since they're really
macros, you have to worry about multiple evaluations of arguments.
In this example, if you tried using something like nextval() as the
new value in an UPDATE, say
    UPDATE a SET x = nextval('foo') WHERE ...
then you'd find the nextval() being evaluated twice, once in the
rule and once when the A table is actually updated (assuming you
you take my advice and change it to a non-INSTEAD rule).  The only
way around that is to use a trigger instead.

            regards, tom lane