undefined | Better HN

0 pointsjcampbell112y ago0 comments

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.

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

0 comments

11 comments · 6 top-level

viraptor12y ago· 4 in thread

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.

AlisdairO12y ago

Or you can use serialisable transaction isolation and retry on conflict.

jeltz12y ago

For the simple cases you do not even need that.

1 more reply

jeltz12y 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.ht...

p4lindromica12y 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.

lucisferre12y ago· 1 in thread

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.

jeltz12y 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 "+".

andrewvc12y 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.

eonil12y 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_chester12y 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?

hrjet12y ago

But then that could be handled by the compatibility layer.

j / k navigate · click thread line to collapse

0 comments

11 comments · 6 top-level

viraptor12y ago· 4 in thread

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.

AlisdairO12y ago

Or you can use serialisable transaction isolation and retry on conflict.

jeltz12y ago

For the simple cases you do not even need that.

1 more reply

jeltz12y 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.ht...

p4lindromica12y 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.

lucisferre12y ago· 1 in thread

That being stated, I'll agree that similar mechanisms for the JSON fields would make sense for Postgres to consider in the future.

jeltz12y 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.

  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 "+".

andrewvc12y 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.

eonil12y 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_chester12y 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?

hrjet12y ago

But then that could be handled by the compatibility layer.

j / k navigate · click thread line to collapse