From: | Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Question about no unchanging update rule + ALTER |
Date: | 2009-02-26 23:23:25 |
Message-ID: | 20090226172325.20855033@prokofiev.trutwins.homeip.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Mayer | 2009-02-26 23:27:12 | Re: speaking of 8.4... |
Previous Message | Tom Lane | 2009-02-26 23:16:49 | Re: Connection refused (0x0000274D/10061). |