There is one tricky part left here. At the point the preceding process stopped, the first session has an open transaction trying to update the row using WHERE s=1. What happens if we try to do something similar in the second session before it's either been committed or rolled back?
$ psql -c "UPDATE t SET i=i+1 WHERE s=1;"
Guess what? This statement will hang! The reason is that we've reached the limits of how many sessions can be isolated from one another safely. Once two sessions are both trying to update the same row, some more complicated rules come into play.
Whenever you try to grab a lock on a specific row--which includes UPDATE, DELETE, and the locks SELECT FOR UPDATE/SELECT FOR DELETE obtain--it has to wait for anyone that already has a lock on that row.
Once the original locker completes its work, then some decisions have to be made.
If the transaction holding the lock rolled back, then no harm done; the newer one continues the originally planned work. But if the original row the second session was trying to update was changed, then what happens next depends on the configuration of your session.
PostgreSQL operates in two modes for resolving the order of operations in these situations. In Read Committed mode, the default, having another row get changed underneath of a session isn't a disaster. What the server will now do is start the original work it was planning to do again with the new copy of the row. If the old row was selected via a WHERE clause, the new one will be checked to see if that condition still applies. If not, the new copy is now ignored--that WHERE clause doesn't find it anymore. But if all of the reasons for why that row was being updated still seem to apply, the planned update will then just execute against the modified copy.
Returning to the example again for a second, if the original transaction commits, it will be the same as if this order of events happened:
UPDATE t SET i=100 WHERE s=1;
UPDATE t SET i=i+1 WHERE s=1;
This means that i=101 is at the end. This is despite the fact that at the point where the second statement started, the other update wasn't complete or visible yet. It will wait for the update that is in the middle of completing and then add its own work on top of that. This is because Read Committed gets a new snapshot of database activity that it might want to pay attention to at the start of every statement. In cases where only reads are being done, this will be a consistent view throughout the whole transaction. In the event of changes by other sessions, some of that new data can enter into your open session this way.