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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
