Thread: Question about no unchanging update rule + ALTER

I found the following on a blog post
(http://www.depesz.com/index.php/2007/09/08/avoiding-empty-updates/)
which had a rule to prevent empty updates:

CREATE RULE no_unchanging_updates AS
ON UPDATE
TO test_table
WHERE ROW(OLD.*) IS NOT DISTINCT FROM ROW(NEW.*)
DO INSTEAD NOTHING;

Works great, but problem comes when I alter the table and add a new
column, it appears the rule doesn't allow an update after adding a
new column via ALTER TABLE ADD COLUMN.

I created the rule above, then did:

ALTER TABLE test_table ADD COLUMN foo TEXT;

=> UPDATE test_table SET foo = 'bar';
UPDATE 0

When doing a \d on the table I notice the rule is expanded at the
time of creation to include each column in an expression, but it is
not updated from the ALTER TABLE command.

Do I have to drop and recreate this rule after every ALTER TABLE
ADD/DELETE column?  Or would the following trigger (also found on
blog post) be a better solution as my app is for a "plugin" builder
where adding/deleting/changing fields is common:

CREATE OR REPLACE FUNCTION prevent_empty_updates() RETURNS trigger as
$BODY$
DECLARE
  BEGIN
    IF ROW(OLD.*) IS DISTINCT FROM ROW(NEW.*) THEN
      RETURN NEW;
    END IF;
    RETURN NULL;
  END;
$BODY$ language plpgsql;

CREATE TRIGGER prevent_empty_updates BEFORE UPDATE ON test FOR EACH
ROW EXECUTE PROCEDURE prevent_empty_updates();

Actually after writing this, this TOO does not seem to work after an
ADD COLUMN.  :/  Any suggestions?

Postgres version is 8.3.

Thanks,

Josh

Josh Trutwin wrote:
> I found the following on a blog post
> (http://www.depesz.com/index.php/2007/09/08/avoiding-empty-updates/)
> which had a rule to prevent empty updates:
>
> CREATE RULE no_unchanging_updates AS
> ON UPDATE
> TO test_table
> WHERE ROW(OLD.*) IS NOT DISTINCT FROM ROW(NEW.*)
> DO INSTEAD NOTHING;
>
> Works great, but problem comes when I alter the table and add a new
> column, it appears the rule doesn't allow an update after adding a
> new column via ALTER TABLE ADD COLUMN.
>
> I created the rule above, then did:
>
> ALTER TABLE test_table ADD COLUMN foo TEXT;
>
> => UPDATE test_table SET foo = 'bar';
> UPDATE 0
>
> When doing a \d on the table I notice the rule is expanded at the
> time of creation to include each column in an expression, but it is
> not updated from the ALTER TABLE command.
>
> Do I have to drop and recreate this rule after every ALTER TABLE
> ADD/DELETE column?

Quite possibly - I seem to remember that id *does* expand the * to an
explicit list of columns. That's what you want sometimes. If the whole
point of the view is to provide a stable interface to an application,
you don't want it changing when you change underlying tables.

> Or would the following trigger (also found on
> blog post) be a better solution as my app is for a "plugin" builder
> where adding/deleting/changing fields is common:
>
> CREATE OR REPLACE FUNCTION prevent_empty_updates() RETURNS trigger as
> $BODY$
> DECLARE
>   BEGIN
>     IF ROW(OLD.*) IS DISTINCT FROM ROW(NEW.*) THEN
>       RETURN NEW;
>     END IF;
>     RETURN NULL;
>   END;
> $BODY$ language plpgsql;
>
> CREATE TRIGGER prevent_empty_updates BEFORE UPDATE ON test FOR EACH
> ROW EXECUTE PROCEDURE prevent_empty_updates();
>
> Actually after writing this, this TOO does not seem to work after an
> ADD COLUMN.  :/  Any suggestions?

Try disconnecting and reconnecting to the database - that should do it.
The function will be "compiled" the first time it is called in a session
, so the * is probably getting expanded then. There's been a lot of work
done to provide automatic re-planning in these sort of situations, but
maybe you're hitting a corner-case.

--
  Richard Huxton
  Archonet Ltd

On Fri, 27 Feb 2009 09:34:08 +0000
Richard Huxton <dev@archonet.com> wrote:

> > CREATE TRIGGER prevent_empty_updates BEFORE UPDATE ON test FOR
> > EACH ROW EXECUTE PROCEDURE prevent_empty_updates();
> >
> > Actually after writing this, this TOO does not seem to work after
> > an ADD COLUMN.  :/  Any suggestions?
>
> Try disconnecting and reconnecting to the database - that should do
> it. The function will be "compiled" the first time it is called in
> a session , so the * is probably getting expanded then. There's
> been a lot of work done to provide automatic re-planning in these
> sort of situations, but maybe you're hitting a corner-case.

Thanks for the info - I'll test out the disconnect and report back.

Josh.