well written article and looks like a great extension. However, my only issue with JSON Schema is that it becomes unsupportable once your JSON objects get too big: to many keys or too many items in an array for example. If you are looking to find "where" the issue is in your JSON object, most schema validators don't provide enough guidance, they just say "something is wrong" as it appears this one does.
Pg_jsonschema – JSON Schema Support for Postgres
41–46 of 46 posts
Re: Pg_jsonschema – JSON Schema Support for Postgres
#42The detail error from example is pretty awful and missing any useful detail.
Is it the limitation of the jsonschema library?
Re: Pg_jsonschema – JSON Schema Support for Postgres
#43PG community had a similar patch, which got reverted from PG 15 on the last minute: https://www.depesz.com/2022/04/06/waiting-for-postgresql-15-...
[deleted]
Re: Pg_jsonschema – JSON Schema Support for Postgres
#44Earlier quoted context omitted.
It’s already quite bad unfortunately. Both support for read replicas and metrics(!) are not in the open source version. They have a prometheus exporter but not on free, afaik. I was also concerned with people reporting memory consumption/leak issues, as I’m planning to have lots of subscriptions. I don’t know haskell well enough, but from the outside it does match the symptoms of having dug themselves into an archite…
(from Hasura) Scaling subscriptions is hard, but we work with our users/customers at scale to make sure settings are tweaked correctly. We have users running 100k - 1M concurrent users in production for live-event type platforms. It's not completely trivial to benchmark and setup because query patterns, streaming vs live queries etc have an impact, but it works very reliably. No missing events, no problems disconnect…
Thanks, that looks reassuring. I will let share my own benchmarks too if I get around to it.
Re: Pg_jsonschema – JSON Schema Support for Postgres
#45PG community had a similar patch, which got reverted from PG 15 on the last minute: https://www.depesz.com/2022/04/06/waiting-for-postgresql-15-...
How can this be used to validate JSON? (and prevent invalid json from being inserted into the database in the first place). I don't see "jsonschema" mentioned in that post.
Perhaps Postgres could support jsonschema validation natively.
Re: Pg_jsonschema – JSON Schema Support for Postgres
#46PG community had a similar patch, which got reverted from PG 15 on the last minute: https://www.depesz.com/2022/04/06/waiting-for-postgresql-15-...
this would be our preference. we'll try to support this for the next commitfest, and if it gets merged then we will deprecate our extension in favour of the native solution.
That JSON_TABLE feature looks pretty useful [0], but it seems complimentary to pg_jsonschema. Can it actually be used to validate a json prior to inserting it to the database?
[0] For my use case, there is a problem: if the json represents a sum type (like Rust enums or Haskell ADTs) as SQL tables. Often you will have a "tag" that specifies which variant the data encodes, and each one has its own properties. When representing as a table, you will usually add the fields of all variants as columns, setting as non-null only the fields belonging to the variant of each row. And the reason I insert jsons into the database is really just to represent sum types in a better way.