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.