Thread: Updating a table via a view
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 ?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
On Mon, 10 Feb 2003, Glen Eustace wrote: glen- there is a package in the contrib directory : postgresql-7.2.2/contrib/spi/timetravel.* or something similar, which does this autoamtically for you. it is very slick. -a > 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 ? > > -- ==================================== | Ara Howard | NOAA Forecast Systems Laboratory | Information and Technology Services | Data Systems Group | R/FST 325 Broadway | Boulder, CO 80305-3328 | Email: ahoward@fsl.noaa.gov | Phone: 303-497-7238 | Fax: 303-497-7259 ====================================
> > 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 > > ); > > ); I suggest you put all things in a pl/pgsql function and callt that function in instead rule. It might be that it takes that ';' as end of the SQL command. HTH Bye Shridhar -- We'll pivot at warp 2 and bring all tubes to bear, Mr. Sulu!
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
Thanks Tom, > Is the base table actually a table? Or is it a view on a_hist? It is a view. > (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. Very good guess :-) Thats very close. Given the above logic, I am not sure I can do what I want with a view. I have previously done this sort of things with triggers on a table but this technique was suggested by a colleage so I thought I'd give it a try. Now that I am aware of the way the rule is constructing the query, I might try to restructure it a bit. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015