3 ms·

(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 motiva

(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.

derefr 4y ago
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."
- lordgilman 4y ago
  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.

(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.

derefr 4y ago

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."

lordgilman 4y ago
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.