5 ms·

It is probably not as simple as "supports json now". Imagine if HN comments were stored as a JSON document: Client A: Read JSON. Client B: Read JSON.

It is probably not as simple as "supports json now". Imagine if HN comments were stored as a JSON document: Client A: Read JSON. Client B: Read JSON. Client A: Append new comment to json document. Client B: Append new comment to json document. Client A: Save JSON Client B: Save JSON A's comment will get deleted. My understanding is that Mongo DB does have a way to append a record within a document, but Postgres does not. I am in no way advocating for MongoDB (I dislike it). I am just saying that I understand that MongoDB has much more sophisticated updates capability than Postgres.

viraptor 13y ago
In that case you can (should) just "SELECT ... FOR UPDATE" in your transaction. This should prevent the issue. Client B will wait with the read until client A commits.
- AlisdairO 13y ago
  Or you can use serialisable transaction isolation and retry on conflict.
  - jeltz 13y ago
    For the simple cases you do not even need that.
    - AlisdairO 13y ago
      Indeed. Depends on whether you need to do a read-update cycle or you can just do it in a single SQL command.
- jeltz 13y ago
  I am pretty sure that just running UPDATE is enough. If an UPDATE happens to get stuck waiting for a lock it is re-evaluated. EDIT: Found where it is explained in the documentation. I do not see why this should not also apply to JSON. http://www.postgresql.org/docs/9.3/static/transaction-iso.html#XACT-READ-COMMITTED http://www.postgresql.org/docs/9.3/static/transaction-iso.ht...
- p4lindromica 13y ago
  ... or you can include an ETag (optimistic lock) in your rows/mongo documents and do a conditional update to ensure no writes are interposed with your reads.
lucisferre 13y ago
Mongo also doesn't have a concept of a transaction so it needs these types of atomic update mechanisms in order to be able to do much of anything sanely. This isn't a problem with SQL which has ACID. I'm not saying that's always the right thing or a good thing, just that your example and conclusion isn't technically correct. Mongo's update capabilities are not more sophisticated but are necessarily different. That being stated, I'll agree that similar mechanisms for the JSON fields would make sense for Postgres to consider in the future.
- jeltz 13y ago
  > That being stated, I'll agree that similar mechanisms for the JSON fields would make sense for Postgres to consider in the future. I think they are working on adding modification functions for hstore right now, and the plan is to also add them to JSON once that is done. You could easily implement your own such functions right now, since PostgreSQL already support atomic modifications in of columns with UPDATE with no overwriting. This statement is safe to use to increment a counter. UPDATE c SET counter = counter + 1 WHERE id = 42; And since that is the case this should also be safe. UPDATE c SET data = my_json_array_append(data, 7) WHERE id = 42; There is nothing magical about "+".
andrewvc 13y ago
Postgres CAN do exactly this, in fact, this is the sort of thing it excels at, the transactions in postgres can do this, and so much more. I think what you're talking about is appending with a single statement, which Postgres can also do. You can deserialize JSON inside a postgres query, alter parts of it, serialize it again, then store it in a single atomic transaction using an UPDATE.
hrjet 13y ago
But then that could be handled by the compatibility layer.
eonil 13y ago
This is exactly what people call ACID TRANSACTION. Which completely lacked on Mongo. MySQL also lacked this, so people had to deal with data loss until they get InnoDB. Next time you choose a database, always check the ACID compliance. It will make your life far easier.
jacques_chester 13y ago
> I am just saying that I understand that MongoDB has much more sophisticated updates capability than Postgres. How many isolation levels does MongoDB support?

viraptor 13y ago

In that case you can (should) just "SELECT ... FOR UPDATE" in your transaction. This should prevent the issue. Client B will wait with the read until client A commits.

AlisdairO 13y ago
Or you can use serialisable transaction isolation and retry on conflict.
- jeltz 13y ago
  For the simple cases you do not even need that.
  - AlisdairO 13y ago
    Indeed. Depends on whether you need to do a read-update cycle or you can just do it in a single SQL command.
jeltz 13y ago
I am pretty sure that just running UPDATE is enough. If an UPDATE happens to get stuck waiting for a lock it is re-evaluated. EDIT: Found where it is explained in the documentation. I do not see why this should not also apply to JSON. http://www.postgresql.org/docs/9.3/static/transaction-iso.html#XACT-READ-COMMITTED http://www.postgresql.org/docs/9.3/static/transaction-iso.ht...
p4lindromica 13y ago
... or you can include an ETag (optimistic lock) in your rows/mongo documents and do a conditional update to ensure no writes are interposed with your reads.

lucisferre 13y ago

Mongo also doesn't have a concept of a transaction so it needs these types of atomic update mechanisms in order to be able to do much of anything sanely. This isn't a problem with SQL which has ACID. I'm not saying that's always the right thing or a good thing, just that your example and conclusion isn't technically correct. Mongo's update capabilities are not more sophisticated but are necessarily different. That being stated, I'll agree that similar mechanisms for the JSON fields would make sense for Postgres to consider in the future.

jeltz 13y ago
> That being stated, I'll agree that similar mechanisms for the JSON fields would make sense for Postgres to consider in the future. I think they are working on adding modification functions for hstore right now, and the plan is to also add them to JSON once that is done. You could easily implement your own such functions right now, since PostgreSQL already support atomic modifications in of columns with UPDATE with no overwriting. This statement is safe to use to increment a counter. UPDATE c SET counter = counter + 1 WHERE id = 42; And since that is the case this should also be safe. UPDATE c SET data = my_json_array_append(data, 7) WHERE id = 42; There is nothing magical about "+".

andrewvc 13y ago

Postgres CAN do exactly this, in fact, this is the sort of thing it excels at, the transactions in postgres can do this, and so much more. I think what you're talking about is appending with a single statement, which Postgres can also do. You can deserialize JSON inside a postgres query, alter parts of it, serialize it again, then store it in a single atomic transaction using an UPDATE.

hrjet 13y ago

But then that could be handled by the compatibility layer.

eonil 13y ago

This is exactly what people call ACID TRANSACTION. Which completely lacked on Mongo. MySQL also lacked this, so people had to deal with data loss until they get InnoDB. Next time you choose a database, always check the ACID compliance. It will make your life far easier.

jacques_chester 13y ago

> I am just saying that I understand that MongoDB has much more sophisticated updates capability than Postgres. How many isolation levels does MongoDB support?