pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing rules to implement backward-compatible schema changes
3+ messages / 2 participants
[nested] [flat]
* Using rules to implement backward-compatible schema changes
@ 2008-01-01 05:51 Mario Becroft <pgsql@becroft.co.nz>
0 siblings, 1 reply; 3+ messages in thread
From: Mario Becroft @ 2008-01-01 05:51 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using rules to implement backward-compatible schema changes
@ 2008-01-01 07:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mario Becroft <pgsql@becroft.co.nz>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2008-01-01 07:09 UTC (permalink / raw)
To: Mario Becroft <pgsql@becroft.co.nz>; +Cc: pgsql-sql
Mario Becroft <pgsql@becroft.co.nz> writes:
> The problem is that when inserting to the view, default values do not
> work as expected.
You need to attach the defaults to the view, viz
ALTER TABLE a ALTER COLUMN bar SET DEFAULT 5;
This is considered a feature not a bug, since you might want a
different default for inserts to the view than for inserts
directly to the underlying table.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using rules to implement backward-compatible schema changes
@ 2008-01-01 07:40 Mario Becroft <pgsql@becroft.co.nz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Mario Becroft @ 2008-01-01 07:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mario Becroft <pgsql@becroft.co.nz>; pgsql-sql
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Mario Becroft <pgsql@becroft.co.nz> writes:
>> The problem is that when inserting to the view, default values do not
>> work as expected.
>
> You need to attach the defaults to the view, viz
>
> ALTER TABLE a ALTER COLUMN bar SET DEFAULT 5;
>
> This is considered a feature not a bug, since you might want a
> different default for inserts to the view than for inserts
> directly to the underlying table.
This crossed my mind, but I could not find a way of specifying defaults
for a view. I see now that it must be done with ALTER TABLE.
Thanks for your help with this problem.
--
Mario Becroft (postgres-related mail) <pgsql@becroft.co.nz>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-01-01 07:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-01 05:51 Using rules to implement backward-compatible schema changes Mario Becroft <pgsql@becroft.co.nz>
2008-01-01 07:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-01 07:40 ` Mario Becroft <pgsql@becroft.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox