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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
