> Hmm, it's easy when you put it like that. Still, each update will be
> converted into a whole host of inserts and deletes, so your N squared
> complexity is still there. One thing is that the rules are always
expanded,
> whether or not any rows match.
create rule xx_2003 as on update to xx_2003
WHERE year(dat_loaded)=2003
do instead ....
The rule is there, but it need not expand in explain if the update has
a "where dat_loaded = '2002-01-01'". (maybe todo: try to collaps plans
to
false in more scenarios ?)
Any solution will need above comparison, the question is imho where the
work should be done. Doing the work with the rules system and
constraints
system would imho have general positive side effects like eliminating
table
scans for data that cannot be in a table because of a check constraint.
One way to make the use of rules easier would be to enhance the rule
system to
allow a "case when then when then else default" syntax in addition to
the
WHERE xxx ACTION part, so you only need 3 rules ?
> Anyway, the general trend seems to be against the idea so I may as
well go
> think of something else :)
Nooo, keep it up :-) e.g. to use inheritance for the table definition is
prbbly
way better than my view thing. So the solution might be an empty master
table
that all others inherit from, and applications use, but has the rules
for insert
/update/delete . By the way, if you don't mind writing a huge rule the
unique
index can be emulated with rules also, by checking existance in all
other
tables with a function that returns an appropriate elog. To make this
efficient
it would be enough to create separate unique indexes on the partial
tables.
Andreas