To: pgsql-sql@postgresql.org
Subject: Using rules to implement backward-compatible schema changes
From: Mario Becroft <pgsql@becroft.co.nz>
Date: Tue, 01 Jan 2008 18:51:30 +1300
Message-ID: <873atit7od.fsf@server.ak.quickcircuit.co.nz>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi postgres experts,

I have encountered what is, to me, a slightly tricky problem when using
rules and I am in need of some help.

I am using rules to simulate an old version of a database schema for
backward-compatibility while migrating to a modified schema.

For instance, there used to be a table a which is now replaced by table
b that contains much the same data. To enable old code to still work, I
create a view called a that simulates the appearance of the old table,
and do instead rules to redirect all operations on it to the actual
table, b.

The problem is that when inserting to the view, default values do not
work as expected. For instance, with a table definition and a rule like
the following:

CREATE TABLE b (
       foo INTEGER,
       bar INTEGER DEFAULT 5
);

CREATE RULE insert AS
 ON INSERT TO a DO INSTEAD
  INSERT INTO b (foo,bar) VALUES (NEW.foo,NEW.bar);

executing the following query:

INSERT INTO a(foo) VALUES (1);

results in bar being NULL instead of 5.

A partial solution is to write the rule as follows:

CREATE RULE insert AS
 ON INSERT TO a DO INSTEAD
  INSERT INTO b (foo,bar) VALUES (NEW.foo,COALESCE(NEW.bar,5));

effectively implementing the default value in the rule. However, this
fails in the case that you explicitly insert NULL. For example:

INSERT INTO a(bar) VALUES (NULL);

results in bar having the value 5 instead of being NULL.

Interestingly, update rules treat columns that appear in the rule but
are omitted from a query as expected, i.e. such columns are not
affected by the query.

Is there a way of getting the behaviour that I want, or is this not
possible using rules?

Am I perhaps approaching this in completely the wrong way? Would there
be a better way of implementing backward-compatibility with the old
table definition?

On a related note, I have noticed that you cannot create triggers on
updateable views. Is there a reason why this could never be possible or
is it not something you should want to do (e.g. because it is not
possible to define clearly what this would mean) or is this essentially
an arbitrary limitation that might change in the future?

I am using version 8.1.

Thanks for any help.

-- 
Mario Becroft (postgres-related mail) <pgsql@becroft.co.nz>