How to update PostgreSQL views
by Dan Dascalescu - 2008-Sep-21
Suppose you have a database tracking user visits to your site. For simplicity, we'll consider this normalized schema:
CREATE TABLE hosts ( id serial PRIMARY KEY, hostname text UNIQUE ); CREATE TABLE urls ( id serial PRIMARY KEY, url text UNIQUE ); CREATE TABLE accesslog ( id serial, visit_time timestamp, host integer REFERENCES hosts, url integer REFERENCES urls, referer integer REFERENCES urls, comment text )
It would also be nice to create a view over accesslog, urls and hosts, so you can nicely see your visits:
CREATE VIEW visits AS SELECT id, visit_time, urls.url, referers.url AS referer, hosts.hostname, comment FROM accesslog JOIN urls ON accesslog.url = urls.id JOIN urls AS referers ON accesslog.referer = referers.id JOIN hosts ON accesslog.host = hosts.id;
You can now run
SELECT * FROM visits ORDER BY visit_time DESC LIMIT 20;
and get a list of the last 20 visits, including what URL on your site they hit and when, what the referer was, and what the visitor's host is. And if you know that a given visit was made by particular person, you can even set
accesslog.comment to, say, 'Joe', if
hostname = 'joesplace.com'. Wait... how do you do that, exactly?
Let's try running an
UPDATE visits SET comment='joe' WHERE hostname='joesplace.com'; ERROR: cannot update a view HINT: You need an unconditional ON UPDATE DO INSTEAD rule.
Indeed, according to the PostgreSQL documentation, "Currently, views are read only: the system will not allow an insert, update, or delete on a view. You can get the effect of an updatable view by creating rules that rewrite inserts, etc. on the view into appropriate actions on other tables". Note that the documentation is vague and scarce with regards to how to create rules to update views. So let's give it a stab anyway... we'll try to set up an "unconditional ON UPDATE DO INSTEAD rule":
-- WRONG, don't actually use this -- CREATE RULE visits_upd AS ON UPDATE TO visits DO INSTEAD UPDATE accesslog SET comment = NEW.comment; -- WRONG, don't actually use this --
And let's run
> UPDATE visits SET comment='Joe' WHERE hostname='joesplace.com'; UPDATE 55900 > -- WTF, we don't have 55900 visits from Joe! > SELECT COUNT(1) FROM visits; count ------- 55900 (1 row) > SELECT COUNT(1) FROM visits WHERE comment <> 'Joe'; count ------- 0 (1 row)
We are, in a word, screwed. We just obliterated all comments we might have set before with 'Joe', not just those for
hostname='joesplace.com'. Hopefully, we were operating on a staging database.
Let's look again at the rule we created. The problem is that it refers to all of
accesslog. Do realize that the
DO INSTEAD statement,
UPDATE accesslog SET comment = NEW.comment
is very much independent and it will simply be executed whenever the rule is triggered. To limit the
DO INSTEAD statement to act only on some records, you need to add a
CREATE RULE visits_upd AS ON UPDATE TO visits DO INSTEAD UPDATE accesslog SET comment = NEW.comment WHERE id = NEW.id
NEW.id is the id from
visits of the row which got updated when the
UPDATE visits happened. Since in the view, that id corresponds to
WHERE id = NEW.id will select from
accesslog only the rows that got updated by the
UPDATE visits statement.
Showing changes from previous revision.