Is `count(*)` ever guaranteed to return the same result in a transaction at any transaction level?

If I create a table.

CREATE TABLE foo AS
SELECT CASE WHEN random() > 0.5 THEN x END AS x
FROM generate_series(1,10) AS x;

And, then I run the following in a transaction

BEGIN;
  SELECT count(*)
  FROM foo
  WHERE x IS NOT NULL;

  --time

  SELECT count(*)
  FROM foo
  WHERE x IS NOT NULL;
END;

Under what transaction level is my result guaranteed to remain the same in the transaction?

My ten cents, and based on the PostgreSQL documentation:

Assumption: your own transaction does not change any relevant value from table foo.

Having the same count(*) in both your queries means:

-
You cannot have dirty reads, because another transaction might have written more rows into your foo table between the first and the second select; and you should not see them.

-
You are not really reading any column, so nonrepeatable reads are not an issue.

-
You cannot have phantom reads, that is, if another transaction changes any row whose x column is NULL to a non-null value, your transaction does not have to notice those changes affecting the WHERE condition.

-
I don't really know how to judge serialization anomalies. My educated guess is that this isn't required. But this is really very open for debate.

Under these conditions, the table from Transaction Isolation Levels makes it clear that Repeatable read complies with the criteria of:

• Not having dirty reads

• Not having non-repeatable reads

• Not having phantom reads (in PostgreSQL, but not demanded by the standard)

... as such, it would give you the same count(*) in both select statements.

StackExchange Database Administrators Q#160231, answer score: 5