5 ms·

hi, author here happy to answer any questions another approach that works great is to use `create type`[1] with an `input_function` and `output_function` to bu

hi, author here happy to answer any questions another approach that works great is to use `create type`[1] with an `input_function` and `output_function` to build on top of an existing scalar type. For example, using that method would allow semver to be persisted as a string natively. The only downside to that is you have to be superuser. [1] https://www.postgresql.org/docs/current/sql-createtype.html https://www.postgresql.org/docs/current/sql-createtype.html

Groxx 4y ago
Is there a way to (reasonably/somewhat ergonomically) describe why a thing failed? True/false without reasons is pretty painful sometimes.
- oliverrice 4y ago
  sure, if you switch the sql functions for plpgsql functions you can use procedural logic to on each segment and `raise exception` with a custom error message that will get returned to the client
mst 4y ago
Why the {1,255} ? Pg's text type is not varchar, it can get a lot longer than that and still index just fine, and while I can see a restriction on the pre_release stuff -maybe- being useful, compilation/build metadata doesn't seem like something where an enforced maximum length that low is necessarily preferable. (I could see URLs, file paths, repo+path+sha etc. type stuff being useful in metadata and not something the code recording it can really control the length of - it may not do any harm in practice at least for a while, but I don't immediately see what -good- the restriction does, albeit I'm perfectly willing to be told "here's the good thing about it that you didn't think of" ;)
- oliverrice 4y ago
  The 255 max length is something you could consider if untrusted people are able to insert data (through an API for example). A column in postgres can store up to 1 GB of data so its a possible DOS vector. Probably not an issue for most use-cases, just something that was considered in the application those code samples came from
  - mst 4y ago
    I would still have gone with a higher length for the meta stuff, given the 2730 character limit on indexing I'd've thought 2048 would probably be fine and much less likely to result in a long path unexpectedly ventilating somebody's foot. If space abuse is a vector under consideration, given there doesn't seem to be a restriction on the number of entries (if I somehow missed one my apologies) you could easily stuff it with a really big array of 255 character entries so I wonder if 2048 plus some sort of reasonably generous limit on the number of elements might not achieve the goal better and provide a better UX/DX as an added bonus. (I'm currently working on a design in a somewhat related area so I've been doing a fair bit of thinking about this - I may of course still be completely wrong, but if so I can at least claim I put effort into ending up wrong ;)
maxloh 4y ago
I once heard that data validation should be implemented in back end server but not datsbase because the latter is more difficult to scale horizontally and usually the bottleneck of transaction performance. Is that true?
iddan 4y ago
Why are these practices not common with web developers? What’s held them back in recent years using it?
- btown 4y ago
  IMO the lack of a Terraform-esque ecosystem of tools around declaratively managing database-objects-as-infrastructure, and understanding how a rollout of a change would be planned, has historically been a big issue. If I'm choosing where in the stack to declare some new business logic or type constraint, I can trivially check that into Git if it's running on an application server, and most frameworks' ORMs can handle column-level migrations. And if I make a mistake, I can push a change that declaratively says what my function signature or DDL should be; it's one of the first things taught to new developers. Terraform extended this mentality to infrastructure, and it was gamechanging. But there are far fewer tutorials and best practices on how to, say, maintain a library of Postgres functions, types, and stored procedures that can be iterated on. I'd venture that most people have no idea how powerful their databases can be.
- progmetaldev 4y ago
  I think most web developers just do not understand SQL well enough to take advantage of these types of features (and if they do, they still may be sticking closer to "standard" ANSI SQL, or may just not be up to date on newer features). Unfortunately, lots of web developers don't get past their ORM abstraction into what is occurring behind the scenes and how to make use of the full range of their database's features.
  - LunaSea 4y ago
    The tooling ecosystem for databases is also terrible. Bad support for testing, for version controlled schemas, language types, etc. This is the reason for the spread of ORMs and similar abstractions to hide this lack of support.
- weird-eye-issue 4y ago
  Way easier handling this in the application code. If I had a developer join my team then commit this into the codebase rather than just using the ORM that everyone else is familiar with they would be fired UNLESS there was a very good reason for doing so

Groxx 4y ago

Is there a way to (reasonably/somewhat ergonomically) describe why a thing failed? True/false without reasons is pretty painful sometimes.

oliverrice 4y ago
sure, if you switch the sql functions for plpgsql functions you can use procedural logic to on each segment and `raise exception` with a custom error message that will get returned to the client

mst 4y ago

Why the {1,255} ? Pg's text type is not varchar, it can get a lot longer than that and still index just fine, and while I can see a restriction on the pre_release stuff -maybe- being useful, compilation/build metadata doesn't seem like something where an enforced maximum length that low is necessarily preferable. (I could see URLs, file paths, repo+path+sha etc. type stuff being useful in metadata and not something the code recording it can really control the length of - it may not do any harm in practice at least for a while, but I don't immediately see what -good- the restriction does, albeit I'm perfectly willing to be told "here's the good thing about it that you didn't think of" ;)

oliverrice 4y ago
The 255 max length is something you could consider if untrusted people are able to insert data (through an API for example). A column in postgres can store up to 1 GB of data so its a possible DOS vector. Probably not an issue for most use-cases, just something that was considered in the application those code samples came from
- mst 4y ago
  I would still have gone with a higher length for the meta stuff, given the 2730 character limit on indexing I'd've thought 2048 would probably be fine and much less likely to result in a long path unexpectedly ventilating somebody's foot. If space abuse is a vector under consideration, given there doesn't seem to be a restriction on the number of entries (if I somehow missed one my apologies) you could easily stuff it with a really big array of 255 character entries so I wonder if 2048 plus some sort of reasonably generous limit on the number of elements might not achieve the goal better and provide a better UX/DX as an added bonus. (I'm currently working on a design in a somewhat related area so I've been doing a fair bit of thinking about this - I may of course still be completely wrong, but if so I can at least claim I put effort into ending up wrong ;)

maxloh 4y ago

I once heard that data validation should be implemented in back end server but not datsbase because the latter is more difficult to scale horizontally and usually the bottleneck of transaction performance. Is that true?

iddan 4y ago

Why are these practices not common with web developers? What’s held them back in recent years using it?

btown 4y ago
IMO the lack of a Terraform-esque ecosystem of tools around declaratively managing database-objects-as-infrastructure, and understanding how a rollout of a change would be planned, has historically been a big issue. If I'm choosing where in the stack to declare some new business logic or type constraint, I can trivially check that into Git if it's running on an application server, and most frameworks' ORMs can handle column-level migrations. And if I make a mistake, I can push a change that declaratively says what my function signature or DDL should be; it's one of the first things taught to new developers. Terraform extended this mentality to infrastructure, and it was gamechanging. But there are far fewer tutorials and best practices on how to, say, maintain a library of Postgres functions, types, and stored procedures that can be iterated on. I'd venture that most people have no idea how powerful their databases can be.
progmetaldev 4y ago
I think most web developers just do not understand SQL well enough to take advantage of these types of features (and if they do, they still may be sticking closer to "standard" ANSI SQL, or may just not be up to date on newer features). Unfortunately, lots of web developers don't get past their ORM abstraction into what is occurring behind the scenes and how to make use of the full range of their database's features.
- LunaSea 4y ago
  The tooling ecosystem for databases is also terrible. Bad support for testing, for version controlled schemas, language types, etc. This is the reason for the spread of ORMs and similar abstractions to hide this lack of support.
weird-eye-issue 4y ago
Way easier handling this in the application code. If I had a developer join my team then commit this into the codebase rather than just using the ORM that everyone else is familiar with they would be fired UNLESS there was a very good reason for doing so