How to update PostgreSQL views

by Dan Dascalescu - 2008-Sep-21

Introduction

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?

How do you update a view?

Let's try running an UPDATE command:

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(1FROM visits;
 count
-------
 55900
(1 row)
> SELECT COUNT(1FROM 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.

Updating PostgreSQL views correctly

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 WHERE clause:

CREATE RULE visits_upd AS
    ON UPDATE TO visits DO INSTEAD UPDATE accesslog SET comment = NEW.comment
    WHERE id = NEW.id

Here, 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 accesslog.id, WHERE id = NEW.id will select from accesslog only the rows that got updated by the UPDATE visits statement.

My tags:
 
Popular tags: