My schema uses table inheritance. I was presenting records to the UI
for insert/update by a defined view
CREATE VIEW monster AS SELECT * FROM
parent_table NATURAL LEFT JOIN child1 NATURAL LEFT JOIN.....
Inserts and updates from the UI were to monster, which then used RULEs
to redirect the query to the correct child, based on a discriminator
column. The UI used the discriminator to hide/show appropriate
widgets.
Well and good until I started to use Hibernate instead of straight
JPA/TopLink. Hibernate checks whether the insert/update affects rows.
With Postgres, since there is an unconditional INSTEAD rule, the
command status is obtained from the last RULE in name alphabetical
order, even if it is a conditional RULE whose WHERE clause did not
match. On getting a zero-insert command status when non-zero was
expected, Hibernate throws an exception. Ouch.
I finally hit upon a workaround that isn't theoretically perfect, but
which I expect to work in all use cases not involving internal coding
errors. Not that I ever make those.
CREATE TABLE dummy(dummy int);
CREATE RULE insert_monster_9998 /* penultimate */ AS
ON INSERT TO monster DO INSTEAD INSERT INTO dummy VALUES(NULL);
CREATE RULE insert_monster_9999 /* last */ AS
ON INSERT TO monster DO INSTEAD DELETE FROM dummy WHERE dummy IS NULL;
Because insert_monster_9999 is a DELETE, its command status is ignored
on INSERT. Rule 9998 is used, and it returns INSERT 0 1. Now, this
will be a false positive when there was no INSERT, but coming from
the UI, the insert should either succeed or be caught by validation
code. (In any event, invalid data would throw and not give an INSERT
0 0 status.)
Update is handled by preloading one non-null row into dummy, and
CREATE RULE update_monster_9999 /* last */ AS
ON UPDATE TO monster DO INSTEAD
UPDATE dummy SET dummy=dummy WHERE dummy IS NOT NULL;
I hope this helps others, and if anyone has a better idea, I'm all
ears.
