There does not seem to be a generic, supported method, but there are some tricks that may be used in limited contexts to evaluate the progress of an individual query. Here are some of them.
When a SELECT or UPDATE query includes any
nextval(sequence_name), or an INSERT has a destination column with a
nextval as default, the current sequence value can be repeatedly queried in another session with
SELECT sequence_name.last_value. It works because sequences are not bounded by transactions. When the execution plan is such that the sequence is incremented linearly during the query, it can be used as a progress indicator.
The pgstattuple contrib module provides functions that can peek directly at the data pages. It appears that when tuples are inserted into an empty table and not yet committed, they are counted in the
dead_tuple_count field from the
Demo with 9.1: create an empty table
CREATE TABLE tt AS (n numeric);
Let's insert 10M rows into it:
INSERT INTO tt SELECT * FROM random() from generate_series(1,10000000);
In another session, check pgstattuple every second during the insert:
$ while true;
do psql -Atc "select dead_tuple_count from pgstattuple('tt')";
It falls back to 0 when the insert is finished (all the tuples become visible and live).
This trick may also be used when the table is not freshly created, but the initial
dead_tuple_count is likely to have a non-zero value and it may also change concurrently if other write activity such as autovacuum is going on (presumably? Not sure what level of concurrency to expect with autovacuum).
However it can't be used if the table is created by the statement itself (
CREATE TABLE ... AS SELECT or
SELECT * INTO newtable), since the creation is transactioned. The workaround would be to create the table with no rows (add
LIMIT 0) and populate it in the next transaction.
pgstattuple doesn't come free: it scans the entire table at every call. Also it's limited to superusers.
In Pavel Stehule's blog, he provides a counter function implemented in C that raises NOTICEs at specified numbers of executions. You have to combine the function with the query somehow to let the executor call it. Notices are sent during the query and they don't need a separate session, only a SQL client that displays them (
psql being the obvious candidate).
Example of INSERT INTO reworked to raise notices:
/* transformation */
INSERT INTO destination_table
FROM (SELECT counter(to_destination_table(_source), 1000, true) r
FROM source _source) x
Related question on stackoverflow, for functions:
How to report progress from long-running PostgreSQL function to client