Hacker News Reader

Comment by derefr | Hacker News Reader

> VALIDATE 1:m

Rather than this, why not do more with existing table REFERENCES metadata? For example, why can't I have a covering index across data from two tables, joined through a foreign-key column with a pre-established REFERENCES foreign-key constraint against the other table, where the REFERENCES constraint then keeps everything in place to make that work (i.e. ensuring that vacuuming/clustering either table also vacuums/clusters the other together in a transaction in order to rewrite the LSNs in the combined index correctly, etc.)

View on HN

(Author here) This is also a neat idea but wouldn't help with subqueries, views or other computed relations getting joined on each other which is a major motivation of the validation proposal.

I could see that being a motivation, but isn't that exactly the use-case where it'd also be a bad idea, overhead-wise?

With a static DDL constraint on join shape, an assertion about such shape can be pre-validated at insert time, to be always-valid at query time, such that queries can then pass/fail such assertions during their "compilation" (query planning) step.

Without such a static constraint, you have to instead insert a merge node in the query plan (same as what a DISTINCT clause does) in order to normalize the input row-tuple-stream and fail on the first non-normalized row-tuple seen.

You could pre-guarantee success in limited cases (e.g. it's going to be a 1:N join if the LHS of the join has a UNIQUE constraint on the single column being joined against); but you're not going to have those guarantees in most cases of "computed relations."

The overhead would be terrible. If implemented, you'd put the syntax in your queries, but a GUC or some such would turn off validation, it'd be off by default (because of that overhead) and you'd only turn it on for local dev and for testing.

The underlying issue, mixing up the cardinality of one side of the join, isn't a super tricky one to identify, especially once you've seen it once or twice or seen what the buggy output looks like. Every intermediate SQL developer has run into the bug and has fixed it. The vision I have for the feature is more like a set of training wheels for developers who are earning their SQL scars. And it's also probably not a terrible idea to document exactly where you are expecting the full M x N cartesian join results when you do want them.

Reply on news.ycombinator.com