4 ms·

This is the second time you’ve said “it’s bad!” And then used a strange hand wavy analogy (in the first post, an xkcd comic) as an out. Again, if you don’t unde

This is the second time you’ve said “it’s bad!” And then used a strange hand wavy analogy (in the first post, an xkcd comic) as an out. Again, if you don’t understand something and don’t want to, just say it, don’t paint it as bad. Enjoy your vastly inefficient and expensive map/reduce configurations if that’s what you like.

lmm 3y ago
You want to be concrete? OK, literally every real-world SQL setup I've seen, when not piggybacking off the application-level infrastructure: - Has no way to deploy a specific historical version of the SQL (e.g. deploy this git tag) - Has no automated checking that the SQL behaves as expected (e.g. this query with this test data should produce this output; if it doesn't, the git tag will not be created). Even checking that the SQL is syntactically valid is rare. - Has no reuse of expressions smaller than a view/table - Has not even basic type checking, e.g. something that alerts if you are combining an expression that might produce null with an expression that does not handle null well - Has not even basic library/dependency management - Has no practical structured values (since none of the infrastructure that would make using ad-hoc temporary tables safe exists). CTEs are an improvement but still thoroughly noncompositional since you can only put them in one place. Let me guess, "these are solved problems" but no details of what you do to solve them, because none of the solutions actually work.
- alex_lav 3y ago
  > - Has no way to deploy a specific historical version of the SQL (e.g. deploy this git tag) Can you clarify this? Do you mean the version of a specific schema/query? > - Has no automated checking that the SQL behaves as expected (e.g. this query with this test data should produce this output; if it doesn't, the git tag will not be created). Even checking that the SQL is syntactically valid is rare. This is part of the purpose of transactions [0] > - Has no reuse of expressions smaller than a view/table Incorrect [1][2][3] > - Has not even basic type checking, e.g. something that alerts if you are combining an expression that might produce null with an expression that does not handle null well Incorrect [4] [5] > - Has not even basic library/dependency management Incorrect [6] > - Has no practical structured values (since none of the infrastructure that would make using ad-hoc temporary tables safe exists). CTEs are an improvement but still thoroughly noncompositional since you can only put them in one place. Can you clarify? How is a transaction + CTE/subquery not sufficient? > Let me guess, "these are solved problems" but no details of what you do to solve them, because none of the solutions actually work. Replace "none of the solutions actually work" with "I don't know how to use them", and yes you're correct. I'm happy to answer more of your questions in good faith, but I'm not really interested in debating a person with their head in the sand. Please let me know if I can help you, if you're actually interested. But also, as I've said, it's also fine to not want/not like this type of stuff. Different strokes and all that. [0] https://www.tutorialspoint.com/sql/sql-transactions.htm https://www.tutorialspoint.com/sql/sql-transactions.htm [1] https://www.postgresql.org/docs/current/sql-createfunction.html https://www.postgresql.org/docs/current/sql-createfunction.h... [2] https://www.postgresql.org/docs/current/sql-createprocedure.html https://www.postgresql.org/docs/current/sql-createprocedure.... [3] https://www.postgresql.org/docs/current/datatype-enum.html https://www.postgresql.org/docs/current/datatype-enum.html [4] https://www.postgresql.org/docs/current/functions-conditional.html https://www.postgresql.org/docs/current/functions-conditiona... [5] https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-nullif/ https://www.postgresqltutorial.com/postgresql-tutorial/postg... [6] https://www.postgresql.org/download/products/6-postgresql-extensions/ https://www.postgresql.org/download/products/6-postgresql-ex...
  - lmm 3y ago
    > Can you clarify this? Do you mean the version of a specific schema/query? I mean being able to version a system implemented in "advanced SQL" the same way I'd version one implemented in an applications language. Make some changes, keep them in VCS, at some point decide to do a release and deploy. A few days later, discover some issue with the changed logic, roll back to the previous release of my "code" (without affecting the data, so not just restoring a database backup). > This is part of the purpose of transactions [0] Right, but the actual workflow tooling around how to use them for this is missing, and for whatever reason the culture that would build and standardise it seems to be missing too. Like, in most ecosystems there's a standardised test-edit cycle that everyone understands; you make your changes and then you run npm test or cargo test or whatever, and you get some assurance that your changes were correct, and that same tooling is also in control of your release workflow and will prevent or at least warn you if you try to do a release where your tests are failing. > Incorrect [1][2][3] Postgresql functions with composite values (and more generally the fact that composite values exist at all) sound like exactly what I was looking for, so that would be a big improvement if I could use them (although for the record they're not a standard SQL feature; MySQL functions can only return a scala value, so there's a major missing middle between functions and views). But even then they have the same problem as temporary tables/views of existing "globally", in the place you'd expect data rather than code to be, and deployment/use tooling being inadequate to use them safely (or at least widely perceived as such). Like, everywhere I've worked has had a de facto rule of "no DDL on the production database except for deliberate long-term changes to the schema that have gone through a review process", and I don't think that's unreasonable (maybe you do?). > Incorrect [4] [5] Those functions exist, I'm talking about having tooling that can tell you where you need to use them. Even in something like Python you have linters that will catch basic mistakes, and they're integrated into the workflow tooling so that you won't accidentally release without running them. > Incorrect [6] A list of libraries != library/dependency management. > Can you clarify? How is a transaction + CTE/subquery not sufficient? If I want to pull out an expression that appears in two arbitrary points in my query, and use it as a CTE, that requires a lot more thought than it would in most languages, because it goes differently depending on whether it was in the SELECT or the WHERE or the GROUP BY or.... The grammar is just somehow less consistent than most programming languages, and the scoping is more confusing, I think because it's kind of lexically backwards (like, you have to declare things to be able to use them, but a lot of the time the declaration goes after the usage. But not always!).

lmm 3y ago

You want to be concrete? OK, literally every real-world SQL setup I've seen, when not piggybacking off the application-level infrastructure: - Has no way to deploy a specific historical version of the SQL (e.g. deploy this git tag) - Has no automated checking that the SQL behaves as expected (e.g. this query with this test data should produce this output; if it doesn't, the git tag will not be created). Even checking that the SQL is syntactically valid is rare. - Has no reuse of expressions smaller than a view/table - Has not even basic type checking, e.g. something that alerts if you are combining an expression that might produce null with an expression that does not handle null well - Has not even basic library/dependency management - Has no practical structured values (since none of the infrastructure that would make using ad-hoc temporary tables safe exists). CTEs are an improvement but still thoroughly noncompositional since you can only put them in one place. Let me guess, "these are solved problems" but no details of what you do to solve them, because none of the solutions actually work.

alex_lav 3y ago
> - Has no way to deploy a specific historical version of the SQL (e.g. deploy this git tag) Can you clarify this? Do you mean the version of a specific schema/query? > - Has no automated checking that the SQL behaves as expected (e.g. this query with this test data should produce this output; if it doesn't, the git tag will not be created). Even checking that the SQL is syntactically valid is rare. This is part of the purpose of transactions [0] > - Has no reuse of expressions smaller than a view/table Incorrect [1][2][3] > - Has not even basic type checking, e.g. something that alerts if you are combining an expression that might produce null with an expression that does not handle null well Incorrect [4] [5] > - Has not even basic library/dependency management Incorrect [6] > - Has no practical structured values (since none of the infrastructure that would make using ad-hoc temporary tables safe exists). CTEs are an improvement but still thoroughly noncompositional since you can only put them in one place. Can you clarify? How is a transaction + CTE/subquery not sufficient? > Let me guess, "these are solved problems" but no details of what you do to solve them, because none of the solutions actually work. Replace "none of the solutions actually work" with "I don't know how to use them", and yes you're correct. I'm happy to answer more of your questions in good faith, but I'm not really interested in debating a person with their head in the sand. Please let me know if I can help you, if you're actually interested. But also, as I've said, it's also fine to not want/not like this type of stuff. Different strokes and all that. [0] https://www.tutorialspoint.com/sql/sql-transactions.htm https://www.tutorialspoint.com/sql/sql-transactions.htm [1] https://www.postgresql.org/docs/current/sql-createfunction.html https://www.postgresql.org/docs/current/sql-createfunction.h... [2] https://www.postgresql.org/docs/current/sql-createprocedure.html https://www.postgresql.org/docs/current/sql-createprocedure.... [3] https://www.postgresql.org/docs/current/datatype-enum.html https://www.postgresql.org/docs/current/datatype-enum.html [4] https://www.postgresql.org/docs/current/functions-conditional.html https://www.postgresql.org/docs/current/functions-conditiona... [5] https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-nullif/ https://www.postgresqltutorial.com/postgresql-tutorial/postg... [6] https://www.postgresql.org/download/products/6-postgresql-extensions/ https://www.postgresql.org/download/products/6-postgresql-ex...
- lmm 3y ago
  > Can you clarify this? Do you mean the version of a specific schema/query? I mean being able to version a system implemented in "advanced SQL" the same way I'd version one implemented in an applications language. Make some changes, keep them in VCS, at some point decide to do a release and deploy. A few days later, discover some issue with the changed logic, roll back to the previous release of my "code" (without affecting the data, so not just restoring a database backup). > This is part of the purpose of transactions [0] Right, but the actual workflow tooling around how to use them for this is missing, and for whatever reason the culture that would build and standardise it seems to be missing too. Like, in most ecosystems there's a standardised test-edit cycle that everyone understands; you make your changes and then you run npm test or cargo test or whatever, and you get some assurance that your changes were correct, and that same tooling is also in control of your release workflow and will prevent or at least warn you if you try to do a release where your tests are failing. > Incorrect [1][2][3] Postgresql functions with composite values (and more generally the fact that composite values exist at all) sound like exactly what I was looking for, so that would be a big improvement if I could use them (although for the record they're not a standard SQL feature; MySQL functions can only return a scala value, so there's a major missing middle between functions and views). But even then they have the same problem as temporary tables/views of existing "globally", in the place you'd expect data rather than code to be, and deployment/use tooling being inadequate to use them safely (or at least widely perceived as such). Like, everywhere I've worked has had a de facto rule of "no DDL on the production database except for deliberate long-term changes to the schema that have gone through a review process", and I don't think that's unreasonable (maybe you do?). > Incorrect [4] [5] Those functions exist, I'm talking about having tooling that can tell you where you need to use them. Even in something like Python you have linters that will catch basic mistakes, and they're integrated into the workflow tooling so that you won't accidentally release without running them. > Incorrect [6] A list of libraries != library/dependency management. > Can you clarify? How is a transaction + CTE/subquery not sufficient? If I want to pull out an expression that appears in two arbitrary points in my query, and use it as a CTE, that requires a lot more thought than it would in most languages, because it goes differently depending on whether it was in the SELECT or the WHERE or the GROUP BY or.... The grammar is just somehow less consistent than most programming languages, and the scoping is more confusing, I think because it's kind of lexically backwards (like, you have to declare things to be able to use them, but a lot of the time the declaration goes after the usage. But not always!).