The best that comes to mind is a constraint trigger, but that would require elaborate locking or the SERIALIZABLE transaction isolation level to be free from race conditions. With JSON, we are pretty much out of luck here. So far, our data model offers no protection against overlapping reservations, which would be good to enforce in the database. Fifth mistake: trying to enforce constraints on JSON This statement will only write a small amount of data.ĭeleting a reservation is just as complicated and expensive, and is left as an exercise to the reader. The whole JSON object has to be read and written, which is more I/O than you would want – particularly if the JSON object is large and stored out of line.Ĭompare how simple the same exercise would be with the junction table: This will fetch the complete JSON object, construct a new JSON from it and store that new object in the table. This data model exemplifies everything that you can do wrong: In this article, I will try to point out good and bad uses of JSON in PostgreSQL, and provide you with guidelines that you can follow. That causes problems and unhappiness in the long run. However, my experience is that the vast majority of people don’t use it correctly. Many people – particularly those with a stronger background in Javascript programming than in relational databases – use it extensively. The comprehensive JSON support in PostgreSQL is one of its best-loved features.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |