Deletions

There's little different that happens during DELETE instead. Close out any open sessions you might have with unfinished transaction seen before. Now let's start another transaction to delete a row:

postgres=# BEGIN;
BEGIN
postgres=# DELETE FROM t WHERE s=1;
DELETE 1
postgres=# SELECT * from t;
 s | i 
---+---
(0 rows)

Once again, if you look at this table from another session, because it's not been committed yet the row deleted here will still be there, just with an xmax value set:

$ psql -c "SELECT *,xmin,xmax FROM t;"
 s |  i  |  xmin  |  xmax  
---+-----+--------+--------
 1 | 101 | 158213 | 158214 

Only in first session has the row been deleted. This brings us to a second form of visibility that needs to be considered: when a row is deleted, it can't actually go away until any session that might need to see the row has ended. So when you commit a deletion, it doesn't actually change the record itself. Instead, it updates the visibility...