marcin
Any way to limit an Ecto map / PostgreSQL JSONB size?
Hello.
I’m using a PostgreSQL-backed Ecto schema with a field :fields, :map, default: %{} to store some custom fields. I’d like to limit the size of this map, to have some control over the storage size of this table.
I am not sure where to add this check in an elegant way:
- The map is deserialized by a GraphQL/Absinthe custom scalar deserializer. I could check for size limit here, but I would have to return just
:errorwhen it’s exceeded, the same as in case of parse error. To the API user this would not be kind - start showing deserialize error after input size crosses a threshold. This approach would also mean i have to define one limit for all JSONBs, regardless of their use. - In resolver, the input is already parsed into a map. That map ends up in changeset - to check it’s size I would have to serialize it to JSON just to check the length of the string, which seems like a waste of CPU cycles.
3… In the DB there is no way I am aware of to limit the size of JSONB column.
Any tips on how to overcome this?
Most Liked
kip
I would implement this as a constraint in the database. Then your application code doesn’t have to do anything. For example:
CREATE TABLE products (
my_json jsonb CONSTRAINT max_jsonb_size CHECK (pg_column_size(my_json) <= 1024)
);
kip
The check_constraint/3 documentation suggests that its role is to convert the database exception into a changeset error. So I suspect your guidance is spot on.
I also didn’t realise that there is migration support for constraints either. Using my example above it could be:
create constraint("products", :max_jsonb_size, check: "pg_column_size(my_json) <= 1024")
outlog
isn’t something like this needed on the changeset? (haven’t kept close track of ecto)
|> check_constraint(:my_json, name: :max_jsonb_size)
marcin
Perfect!
I found some stack overflow where they did this with a TRIGGER, and was discouraged as that looked complex. CONSTRAINT CHECK is the proper solution I was looking for. Thanks!
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"