Dirty Reads in Postgres

Question

I have a long running function that should be inserting new rows. How do I check the progress of this function?

I was thinking dirty reads would work so I read http://www.postgresql.org/docs/8.4/interactive/sql-set-transaction.html and came up with the following code and ran it in a new session:

SET SESSION CHARACTERISTICS AS SERIALIZABLE;

SELECT * FROM MyTable;

Postgres gives me a syntax error. What am I doing wrong? If I do it right, will I see the inserted records while that long function is still running?

Thanks.

Answer 1

PostgreSQL does not implement a way for you to see this from outside the function, aka READ UNCOMMITTED isolation level. Your basic two options are:

• Have the function use RAISE NOTICE every now and then to show you how far along you are
• Use something like dblink from the function back to the same database, and update a counter table from there. Since that's a completely separate transaction, the counter will be visible as soon as that transaction commits - you don't have to wait for the main transaction (around the function call) to finish.

Answer 2

For versions up to 9.0: PostgreSQL Transaction Isolation

In PostgreSQL, you can request any of the four standard transaction isolation levels. But internally, there are only two distinct isolation levels, which correspond to the levels Read Committed and Serializable. When you select the level Read Uncommitted you really get Read Committed, and when you select Repeatable Read you really get Serializable, so the actual isolation level might be stricter than what you select. This is permitted by the SQL standard: the four isolation levels only define which phenomena must not happen, they do not define which phenomena must happen.

For versions from 9.1 to current(15): PostgreSQL Transaction Isolation

In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.

Answer 3

Dirty read doesn't occur in PostgreSQL even the isolation level is READ UNCOMMITTED. And, the documentation says below:

PostgreSQL's Read Uncommitted mode behaves like Read Committed.

So, READ UNCOMMITTED has the same characteristics of READ COMMITTED in PostgreSQL different from other databases so in short, READ UNCOMMITTED and READ COMMITTED are the same in PostgreSQL.

And, this table below shows which anomaly occurs in which isolation level in PostgreSQL according to my experiments:

Anomaly	Read Uncommitted	Read Committed	Repeatable Read	Serializable
Dirty Read	No	No	No	No
Non-repeatable Read	Yes	Yes	No	No
Phantom Read	Yes	Yes	No	No
Lost Update	Yes	Yes	No	No
Write Skew (Serialization Anomaly)	Yes	Yes	Yes	No

With SELECT FOR UPDATE:

Anomaly	Read Uncommitted	Read Committed	Repeatable Read	Serializable
Dirty Read	No	No	No	No
Non-repeatable Read	No	No	No	No
Phantom Read	No	No	No	No
Lost Update	No	No	No	No
Write Skew (Serialization Anomaly)	No	No	No	No