[openstreetmap-website] Fix bug allowing created elements to reference deleted ones. (#992)

[Matt Amos]

If it's possible to have `current_way_nodes.node_id` FK reference `current_nodes.node_id WHERE visible`, then yes. Last time I looked, I wasn't able to see how to do it without stuff like creating a unique index on a materialized view just for FK checking.

Additionally, we'd need a fairly big schema change (or more materialized views) to support doing this for relation members, as currently that table "references" three different tables.

For the materialized views + indexes approach, it's unclear to me how much extra space and time that would take up on the server. The docs seem to suggest a materialized view takes the same space as if it were a table, and I assume the same for any index on it (despite the fact that it's a strict subset of the PK). This could, it seems to me, be slower than just using row locking.

The "real fix" here is to stop storing deleted items in the `current_*` tables at all (and split relation members by type). But that breaks the implementation of the deleted items call, etc... See discussions _passim_ about how this has been on the TODO list since approximately forever :wink:


---
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/pull/992#issuecomment-111651328
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20150612/af7d7056/attachment.html>