Thread: Update on tables when the row doesn't change
Hi all, maybe it's a very silly question, but why does Postgres perform an update on the table even if no data changes? I recognized this recently doing a rewrite of my rules because they took to long. I had many conditional rules I collapsed to one unconditional rule, so that the views get constructed only once. If I split these updates to the underlying tables, I get a lot of updates which don't perform any "real" updates. Can I circumvent this behaviour of Postgres only by defining lot of rules / triggers on these underlying table are there some trickier ways? Any help appreciated, Sebastian
I'm sure I'm not the only one, but, what are you talking about? RULEs are not really obvious so it would help if you could post an example of what you mean... Have a nice day, On Tue, May 24, 2005 at 05:05:34PM +0200, Sebastian Böck wrote: > Hi all, > > maybe it's a very silly question, but why does Postgres perform an > update on the table even if no data changes? > > I recognized this recently doing a rewrite of my rules because they > took to long. I had many conditional rules I collapsed to one > unconditional rule, so that the views get constructed only once. If I > split these updates to the underlying tables, I get a lot of updates > which don't perform any "real" updates. > > Can I circumvent this behaviour of Postgres only by defining lot of > rules / triggers on these underlying table are there some trickier ways? > > Any help appreciated, > > Sebastian > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout wrote: > I'm sure I'm not the only one, but, what are you talking about? RULEs > are not really obvious so it would help if you could post an example of > what you mean... > > Have a nice day, Hi, I'm not really talking about rules. I'm talking about updates on *real* tables, and how to avoid unnecessary updates on these tables if the row doesn't change. The situation looks like this: I have a view which is a join of a lot of tables. I have lot of conditional ON UPDATE rules to that view that split one update to the view into updates on the underlying table. The condition of each rule is constructed in a way that the underlying table only gets an update if the corresponding values change. If I collapse all these rules into one conditional rule and pass all the updates to the underlying tables, I get a lot of unnecessary updates to these real tables, if the values don't change. Thats what I want to avoid. Sorry for not beeing that clear. Sebastian
On 5/24/05, Sebastian Böck <sebastianboeck@freenet.de> wrote: > Martijn van Oosterhout wrote: > > I'm sure I'm not the only one, but, what are you talking about? RULEs > > are not really obvious so it would help if you could post an example of > > what you mean... > > > > Have a nice day, > > Hi, I'm not really talking about rules. > > I'm talking about updates on *real* tables, and how to avoid > unnecessary updates on these tables if the row doesn't change. > > The situation looks like this: > > I have a view which is a join of a lot of tables. > > I have lot of conditional ON UPDATE rules to that view that split one > update to the view into updates on the underlying table. The condition > of each rule is constructed in a way that the underlying table only > gets an update if the corresponding values change. > > If I collapse all these rules into one conditional rule and pass all > the updates to the underlying tables, I get a lot of unnecessary > updates to these real tables, if the values don't change. > > Thats what I want to avoid. > > Sorry for not beeing that clear. > > Sebastian > > And how are you preventing the rule execute the update if the field has no change? That is way Martijn told you about showing the rule. AFAIK, if you execute an update on a view that has a ON UPDATE rule all the SQL ACTIONS specified for the rule will be perfomed INSTEAD OF the original update -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
Jaime Casanova wrote: > On 5/24/05, Sebastian Böck <sebastianboeck@freenet.de> wrote: > >>Martijn van Oosterhout wrote: >> >>>I'm sure I'm not the only one, but, what are you talking about? RULEs >>>are not really obvious so it would help if you could post an example of >>>what you mean... I attach some sample SQL (commented) to demonstrate the described scenario: >>Hi, I'm not really talking about rules. >> >>I'm talking about updates on *real* tables, and how to avoid >>unnecessary updates on these tables if the row doesn't change. >> >>The situation looks like this: >> >>I have a view which is a join of a lot of tables. >> >>I have lot of conditional ON UPDATE rules to that view that split one >>update to the view into updates on the underlying table. The condition >>of each rule is constructed in a way that the underlying table only >>gets an update if the corresponding values change. >> >>If I collapse all these rules into one conditional rule and pass all >>the updates to the underlying tables, I get a lot of unnecessary >>updates to these real tables, if the values don't change. >> >>Thats what I want to avoid. >> >>Sorry for not beeing that clear. >> >>Sebastian >> >> > > And how are you preventing the rule execute the update if the field > has no change? That is way Martijn told you about showing the rule. Sorry I don't understand what you mean. What's wrong with: CREATE OR REPLACE RULE upd AS ON UPDATE TO view WHERE NEW.field = OLD.field DO INSTEAD NOTHING; > AFAIK, if you execute an update on a view that has a ON UPDATE rule > all the SQL ACTIONS specified for the rule will be perfomed INSTEAD OF > the original update Ok thanks for the tip, but I alredy knew this ;) But my original question remeins: Why does Postgres perform updates to tables, even if the row doesn't change at all? Thanks Sebastian
Sorry, missed the SQL to test.
Sebastian
/* tables */
CREATE TABLE test (
id INTEGER PRIMARY KEY,
test TEXT NOT NULL
);
CREATE TABLE join1 (
id INTEGER PRIMARY KEY,
text1 TEXT NOT NULL
);
CREATE TABLE join2 (
id INTEGER PRIMARY KEY,
text2 TEXT NOT NULL
);
CREATE TABLE join3 (
id INTEGER PRIMARY KEY,
text3 TEXT NOT NULL
);
/* view */
CREATE OR REPLACE VIEW view_test AS
SELECT
id,
test,
text1,
text2,
text3
FROM test
LEFT JOIN join1 USING (id)
LEFT JOIN join2 USING (id)
LEFT JOIN join3 USING (id);
/* data */
INSERT INTO test (id) VALUES ('1','Test 1');
INSERT INTO test (id) VALUES ('2','Test 2');
INSERT INTO test (id) VALUES ('3','Test 3');
INSERT INTO join1 (id,text1) VALUES ('1','Test 1 1');
INSERT INTO join1 (id,text1) VALUES ('2','Test 1 2');
INSERT INTO join1 (id,text1) VALUES ('3','Test 1 3');
INSERT INTO join2 (id,text2) VALUES ('1','Test 2 1');
INSERT INTO join2 (id,text2) VALUES ('2','Test 2 2');
INSERT INTO join2 (id,text2) VALUES ('3','Test 2 3');
INSERT INTO join3 (id,text3) VALUES ('1','Test 3 1');
INSERT INTO join3 (id,text3) VALUES ('2','Test 3 2');
INSERT INTO join3 (id,text3) VALUES ('3','Test 3 3');
/* 1st way of separating updates
pro: no unnecessary updates on tables
con: the view gets evaluated 4 times
This was the whole thing being before change.
This can get *really* slow, if the view itself is not the fastest.
*/
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD NOTHING;
CREATE OR REPLACE RULE upd_ AS ON UPDATE TO view_test
WHERE NEW.test <> OLD.test
DO UPDATE test
SET test = NEW.test
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_1 AS ON UPDATE TO view_test
WHERE NEW.text1 <> OLD.text1
DO UPDATE join1
SET text1 = NEW.text1
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_2 AS ON UPDATE TO view_test
WHERE NEW.text2 <> OLD.text2
DO UPDATE join2
SET text2 = NEW.text2
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_3 AS ON UPDATE TO view_test
WHERE NEW.text3 <> OLD.text3
DO UPDATE join3
SET text3 = NEW.text3
WHERE id = OLD.id;
/* 2nd way of separating updates
pro: ?
con: the view gets evaluated 4 times, why?
unnecessary updates on tables
First approach to reduce execution time of update, but view gets
also evaluated 4 times (no performance boost).
Here I discovered the problem that all underlying tables are getting
the updates, even if the data in that table doesn't change.
This can hurt you as well, if you log all updates.
*/
DROP RULE upd_ ON view_test;
DROP RULE upd_1 ON view_test;
DROP RULE upd_2 ON view_test;
DROP RULE upd_3 ON view_test;
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD (
UPDATE test SET test = NEW.test WHERE id = OLD.id;
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
);
/* 3rd way of separating updates
con: unnecessary updates on tables
pro: view gets evaluated only 1 time
Not adressing the problem of unnecessary updates, but the view
gets only evaluated one time.
*/
CREATE OR REPLACE FUNCTION upd (view_test) RETURNS VOID AS $$
DECLARE
NEW ALIAS FOR $1;
BEGIN
RAISE NOTICE 'UPDATE';
UPDATE test SET test = NEW.test WHERE id = OLD.id;
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD SELECT upd (NEW.*);
/* 4th way of doing it
pro: view gets evaluated only 1 time
no unnecessary updates on tables
con: ??
Here is the way I solved all my performance problems.
Only remainig issue: How can I eliminate the response of the select?
*/
CREATE OR REPLACE FUNCTION upd (view_test, view_test) RETURNS VOID AS $$
DECLARE
NEW ALIAS FOR $1;
OLD ALIAS FOR $2;
BEGIN
IF (NEW.test <> OLD.test) THEN
RAISE NOTICE 'UPDATE test';
UPDATE test SET test = NEW.test WHERE id = OLD.id;
END IF;
IF (NEW.text1 <> OLD.text1) THEN
RAISE NOTICE 'UPDATE join1';
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
END IF;
IF (NEW.text2 <> OLD.text2) THEN
RAISE NOTICE 'UPDATE join2';
UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
END IF;
IF (NEW.text3 <> OLD.text3) THEN
RAISE NOTICE 'UPDATE join3';
UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD SELECT upd (NEW.*,OLD.*);
=?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck@freenet.de> writes:
> Why does Postgres perform updates to tables, even if the row doesn't
> change at all?
Because testing for this would almost surely be a net loss for the vast
majority of applications. Checking to see if the new row value exactly
equals the old is hardly a zero-cost operation; if you pay that on every
update, that's a lot of overhead that you are hoping to make back by
sometimes avoiding the physical store of the new tuple. In most
applications I think the "sometimes" isn't going to be often enough
to justify doing it.
If you have a particular table in a particular app where it is worth it,
I'd recommend writing a BEFORE UPDATE trigger to make the comparisons
and suppress the update when NEW and OLD are equal.
regards, tom lane
Tom Lane wrote: > =?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck@freenet.de> writes: > >>Why does Postgres perform updates to tables, even if the row doesn't >>change at all? > > > Because testing for this would almost surely be a net loss for the vast > majority of applications. Checking to see if the new row value exactly > equals the old is hardly a zero-cost operation; if you pay that on every > update, that's a lot of overhead that you are hoping to make back by > sometimes avoiding the physical store of the new tuple. In most > applications I think the "sometimes" isn't going to be often enough > to justify doing it. > > If you have a particular table in a particular app where it is worth it, > I'd recommend writing a BEFORE UPDATE trigger to make the comparisons > and suppress the update when NEW and OLD are equal. In any case, what if I have a trigger that's supposed to increment a counter or similar if issue a supposedly "unneeded" update. -- Richard Huxton Archonet Ltd
On 5/24/05, Sebastian Böck <sebastianboeck@freenet.de> wrote:
> /* 3rd way of separating updates
> con: unnecessary updates on tables
> pro: view gets evaluated only 1 time
>
> Not adressing the problem of unnecessary updates, but the view
> gets only evaluated one time.
>
> */
>
> CREATE OR REPLACE FUNCTION upd (view_test) RETURNS VOID AS $$
> DECLARE
> NEW ALIAS FOR $1;
> BEGIN
> RAISE NOTICE 'UPDATE';
> UPDATE test SET test = NEW.test WHERE id = OLD.id;
> UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
> UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
> UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
Control question, I didn't check it, but would it be enough to change from:
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
to:
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 <> NEW.text1?
... I may be wrong. :)
Regards,
Dawid
Dawid Kuroczko wrote: > On 5/24/05, Sebastian Böck <sebastianboeck@freenet.de> wrote: > >>/* 3rd way of separating updates >> con: unnecessary updates on tables >> pro: view gets evaluated only 1 time >> >> Not adressing the problem of unnecessary updates, but the view >> gets only evaluated one time. >> >>*/ >> >>CREATE OR REPLACE FUNCTION upd (view_test) RETURNS VOID AS $$ >> DECLARE >> NEW ALIAS FOR $1; >> BEGIN >> RAISE NOTICE 'UPDATE'; >> UPDATE test SET test = NEW.test WHERE id = OLD.id; >> UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id; >> UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id; >> UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id; >> RETURN; >> END; >>$$ LANGUAGE plpgsql; > > > Control question, I didn't check it, but would it be enough to change from: > UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id; > to: > UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 <> NEW.text1? > > ... I may be wrong. :) Yes, thats more elegant then my other (4th) solution. Was late yesterday evening ;) Thanks Sebastian
Well, I havn't run the rule so I don't know exactly whether it is relevent, but simply put, RULEs are like *macro substitution*. In macros, if you specify an expression (like a view) as an argument, it is placed as a whole each place the argument is used. UPDATEs for different tables cannot be merged into a single query so you get four queries and it's not clear how you could avoid any work. I guess each subquery may be optimised individually, though I don't know if it really helps. Perhaps you could show us the resulting query plans and how you think they could be improved. Have a nice day, On Wed, May 25, 2005 at 10:40:38AM +0200, Sebastian Böck wrote: > Thank you for the explanation. That's what I wanted to do first, but > then I discovered that the view gets not only evaluated for every rule > on it, but also gets evaluated as often as there are statements in one > rule. > > Example: > > CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test > DO INSTEAD ( > UPDATE test SET test = NEW.test WHERE id = OLD.id; > UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id; > UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id; > UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id; > ); > > Why gets the view evaluated 4 times? > > Thanks > > Sebastian -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout wrote: > Well, I havn't run the rule so I don't know exactly whether it is > relevent, but simply put, RULEs are like *macro substitution*. In > macros, if you specify an expression (like a view) as an argument, it > is placed as a whole each place the argument is used. Yes I understood it the same way. That makes absolutly sense in situations where you have different rules on the same target (view or table). > UPDATEs for different tables cannot be merged into a single query so > you get four queries and it's not clear how you could avoid any work. > > I guess each subquery may be optimised individually, though I don't > know if it really helps. Perhaps you could show us the resulting query > plans and how you think they could be improved. There are a lot of geometric operations involved (all done via postgis) that make the view slow. I don't want to change the view itself now, only want to get rid of all the unnecessary evaluations of the view. So I tried to collapse the rules into on rule as shown in the example below: > On Wed, May 25, 2005 at 10:40:38AM +0200, Sebastian Böck wrote: > >>CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test >> DO INSTEAD ( >> UPDATE test SET test = NEW.test WHERE id = OLD.id; >> UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id; >> UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id; >> UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id; >> ); I hoped that the view gets only evaluated once, because it is only one rule, but thats not true :( Thanks for all so far, I'll come back when tuning the obove mentioned queries. Sebastian
On 5/25/05, Sebastian Böck <sebastianboeck@freenet.de> wrote:
> >>CREATE OR REPLACE FUNCTION upd (view_test) RETURNS VOID AS $$
> >> DECLARE
> >> NEW ALIAS FOR $1;
> >> BEGIN
> >> RAISE NOTICE 'UPDATE';
> >> UPDATE test SET test = NEW.test WHERE id = OLD.id;
> >> UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
> >> UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
> >> UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
> >> RETURN;
> >> END;
> >>$$ LANGUAGE plpgsql;
> >
> >
> > Control question, I didn't check it, but would it be enough to change from:
> > UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
> > to:
> > UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 <> NEW.text1?
> >
> > ... I may be wrong. :)
>
> Yes, thats more elegant then my other (4th) solution.
> Was late yesterday evening ;)
Be wary of the NULL values though. :) Either don't use them, add
something like 'AND (text1 <> NEW.text1 OR text1 IS NULL OR NEW.text1
IS NULL)' or something more complicated. :)
Regards,
Dawid
Dawid Kuroczko wrote:
>>>Control question, I didn't check it, but would it be enough to change from:
>>> UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
>>>to:
>>> UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 <> NEW.text1?
>>>
>>>... I may be wrong. :)
>>
>>Yes, thats more elegant then my other (4th) solution.
>>Was late yesterday evening ;)
>
>
> Be wary of the NULL values though. :) Either don't use them, add
> something like 'AND (text1 <> NEW.text1 OR text1 IS NULL OR NEW.text1
> IS NULL)' or something more complicated. :)
Thanks for the notice, but I have a special operator for this:
CREATE OR REPLACE FUNCTION different (ANYELEMENT, ANYELEMENT) RETURNS
BOOLEAN AS $$
BEGIN
IF ($1 <> $2) OR ($1 IS NULL <> $2 IS NULL) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE OPERATOR <<>> (
LEFTARG = ANYELEMENT,
RIGHTARG = ANYELEMENT,
PROCEDURE = different,
COMMUTATOR = <<>>,
NEGATOR = ====
);
Sebastian
On Wed, 2005-05-25 at 13:09 +0200, Sebastian Böck wrote: > Dawid Kuroczko wrote: > > > > > Be wary of the NULL values though. :) Either don't use them, add > > something like 'AND (text1 <> NEW.text1 OR text1 IS NULL OR NEW.text1 > > IS NULL)' or something more complicated. :) > > Thanks for the notice, but I have a special operator for this: > > CREATE OR REPLACE FUNCTION different (ANYELEMENT, ANYELEMENT) RETURNS and then there is the 'IS DISTINCT FROM' construct http://www.postgresql.org/docs/8.0/static/functions-comparison.html#AEN6094 gnari
Ragnar Hafstað wrote: > On Wed, 2005-05-25 at 13:09 +0200, Sebastian Böck wrote: > >>Dawid Kuroczko wrote: >> >> >>>Be wary of the NULL values though. :) Either don't use them, add >>>something like 'AND (text1 <> NEW.text1 OR text1 IS NULL OR NEW.text1 >>>IS NULL)' or something more complicated. :) >> >>Thanks for the notice, but I have a special operator for this: >> >>CREATE OR REPLACE FUNCTION different (ANYELEMENT, ANYELEMENT) RETURNS > > > and then there is the 'IS DISTINCT FROM' construct > http://www.postgresql.org/docs/8.0/static/functions-comparison.html#AEN6094 > Much easier :) Thanks Sebastian
Hi All,
Can someone please address this aspect of Sebastian's email? I, too, am
interested in the response.
>> Why does Postgres perform an update on the table even
>> if no data changes?
>> Can I circumvent this behaviour of Postgres?
Tim
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sebastian Böck
Sent: Tuesday, May 24, 2005 8:06 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Update on tables when the row doesn't change
Hi all,
Maybe it's a very silly question, but why does Postgres perform an update on
the table even if no data changes?
I recognized this recently doing a rewrite of my rules because they took to
long. I had many conditional rules I collapsed to one unconditional rule, so
that the views get constructed only once. If I split these updates to the
underlying tables, I get a lot of updates which don't perform any "real"
updates.
Can I circumvent this behaviour of Postgres only by defining lot of rules /
triggers on these underlying table are there some trickier ways?
Any help appreciated,
Sebastian
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Wed, 2005-05-25 at 10:41, Tim Vadnais wrote: > >> Why does Postgres perform an update on the table even > >> if no data changes? > >> Can I circumvent this behaviour of Postgres? > >> > Hi All, > > Can someone please address this aspect of Sebastian's email? I, too, am > interested in the response. > Actually, I believe it was addressed by Tom, when he said that it would be more costly to check every single update to see if there WAS a change before applying it than to just apply the changes every time. I concur. Can you imagine slowing down ALL updates by 5% or something like that just to prevent the rare case where an update didn't actually change a value?
On Wed, May 25, 2005 at 08:41:23AM -0700, Tim Vadnais wrote: > Hi All, > > Can someone please address this aspect of Sebastian's email? I, too, am > interested in the response. > > >> Why does Postgres perform an update on the table even > >> if no data changes? > >> Can I circumvent this behaviour of Postgres? Tom did, AFAIK. Basically, it's a non-zero cost to check for something that's useless in 99.99% of cases. Secondly, if you have a update trigger on the table, it applies to all updates, even if they don't change the actual data. If you didn't want to update the row, don't issue it in the first place. If you want to block the update, create your own trigger to detect it and drop it. Most people don't need it. That's completely seperate to his issue with reevaluating the view, where I've seen no response to the question about what he thinks PostgreSQL should be doing. You know, the query plan he get vs what he actually wants. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.