[SOLVED] Dirty Reads in Postgres

Dirty Reads in Postgres

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.

Solution

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.