An observation related to the performance discussion:
I ran the following queries on a reasonably large Oracle database (45000 items, aroudn 100000 revisions)
SELECT count(*)
FROM cr_items ci, cr_revisions cr
WHERE ci.item_id = cr.item_id AND
ci.live_revision = cr.revision_id ;
Took 8 seconds
SELECT count(*)
FROM cr_items ci, cr_revisions cr
WHERE
ci.live_revision = cr.revision_id ;
Took 0 seconds 😊
This is a little worrying, since the first version is de facto what we would be doing if the view was extended to include live_revision, and this was then used to find the live ones.
I think it is quite obvious what happens here: Oracle isn't using the best index available, because it doesn't know of the implicit assumption that the live revision of an item is a revision of exactly that item.
Solution: Tell Oracle about the logical link between live revisions and their associated items:
/* This constraint logically follows from the uniqueness of revision_id. But Oracle is too stupid to see that*/
ALTER TABLE cr_revisions ADD (CONSTRAINT cr_revisions_rev_item_unq
UNIQUE (revision_id, item_id));
/* This is what we really want to tell Oracle */
ALTER TABLE cr_items
ADD (CONSTRAINT cr_items_live_item_fk
FOREIGN KEY (live_revision, item_id)
REFERENCES cr_revisions (revision_id, item_id));
After adding this constraints, both the above queries return "immediately".
The second constraint could logically replace the current foreign key constraint on live_revision (cr_items_live_fk) - since it is in fact a strengthening of that constraint. It might be good to test performance of existing code.
All the above goes for latest revisions as well
Looking at postgres, it looks like we are NOT using declarative referential integrity for this case at all - but are instead relying on triggers. There is a comment saying:
-- postgresql RI bug causes multiple failures with regards to deletion of
-- content_revisions (DanW mailto:dcwickstrom@earthlink.net)
Does anyone know
* Which bug this is?
* If it has been fixed, and if so in which version?
