3 ms·

So you're saying that a SELECT statement that happens strictly after an INSERT statement (on the same connection) can possibly not see the data that was written

by tomp 9y ago

So you're saying that a SELECT statement that happens strictly after an INSERT statement (on the same connection) can possibly not see the data that was written by the INSERT statement? I guess that would be possible, but I would be very surprised if that was so... Also, I would also expect writes to rows to be atomic (i.e. you wouldn't see a row with half-written data, or a row that has an `id` but not (yet) a `name`) - again, that kind of behaviour would be possible, but very surprising to the point of the DB being unusable.

fusiongyro 9y ago
This is why almost all ORMs create a transaction and have a "unit-of-work" that encompasses a single transaction. Race conditions are always surprising. Fortunately, we have a simple remedy for that: transactions. :) Suppose you have another connection and it runs DELETE FROM t WHERE id = (SELECT MAX(id) FROM t). If that winds up in the middle, it could screw things up for you. Is it likely to happen? No, but again... the remedy is simple. Also, the scenario you describe seems simple enough, but what happens when you bulk insert a few thousand rows? By the way, with Postgres, there really is no way to not have a transaction. If you don't issue BEGIN, it treats the statement as a small transaction. So you're getting the same overhead on a single statement, if your plan was to somehow avoid the overhead. If this makes you question the database's usability, I have some bad news for you: all ACID compliant databases work this way. The "atomic" unit is the transaction, not the connection. The consistency guarantees pertain to the transaction, not the connection. This is not a weird thing about Postgres, this is how they all work (except, of course, that MySQL can't always roll back aspects of a transaction, like DDL).

3 ms·

So you're saying that a SELECT statement that happens strictly after an INSERT statement (on the same connection) can possibly not see the data that was written

by tomp 9y ago

fusiongyro 9y ago
This is why almost all ORMs create a transaction and have a "unit-of-work" that encompasses a single transaction. Race conditions are always surprising. Fortunately, we have a simple remedy for that: transactions. :) Suppose you have another connection and it runs DELETE FROM t WHERE id = (SELECT MAX(id) FROM t). If that winds up in the middle, it could screw things up for you. Is it likely to happen? No, but again... the remedy is simple. Also, the scenario you describe seems simple enough, but what happens when you bulk insert a few thousand rows? By the way, with Postgres, there really is no way to not have a transaction. If you don't issue BEGIN, it treats the statement as a small transaction. So you're getting the same overhead on a single statement, if your plan was to somehow avoid the overhead. If this makes you question the database's usability, I have some bad news for you: all ACID compliant databases work this way. The "atomic" unit is the transaction, not the connection. The consistency guarantees pertain to the transaction, not the connection. This is not a weird thing about Postgres, this is how they all work (except, of course, that MySQL can't always roll back aspects of a transaction, like DDL).