Any way to limit an Ecto map / PostgreSQL JSONB size?

Questions / Help
,
1

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:

  1. 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 :error when 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.
  2. 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?

2

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)
);
8 Likes
3

isn’t something like this needed on the changeset? (haven’t kept close track of ecto)

|> check_constraint(:my_json, name: :max_jsonb_size)

2 Likes
4

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")
5 Likes
5

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!

1 Like