Sometimes there is a need to execute a long running update statement. This update statement might be modifying millions of rows as was the case when we went hunting for a way to track the progress of the update. Hunting around took us to http://archives.postgresql.org/pgsql-admin/2002-07/msg00286.php In our particular case, we are using postgresql but this should work with any database server that provides sequences. Our original sql was of the form:

1
2
3
4

update only table1 t1
set amount = t2.price
from table2 t2
where t1.id = t2.id;

There is of course now way of figuring out how many rows had been updated already. The first step was to create a sequence

1

CREATE TEMPORARY SEQUENCE seq_progress START 1;

We can then use this sequence in the update statement to ensure that each row updated also increments the sequence

1
2
3
4
5

update only table1 t1
set amount = t2.price
from table2 t2
where nextval('seq_progress') != 0
and t1.id = t2.id;

Once the query is running, you can open another connection to the database. To get an indication of how far it has got, you can just run the following

1

select nextval('seq_progress');

Bear in mind that this will also increment it by 1 but if you have millions of rows which is really the only case in which this would be useful, a few additional increments is hardly going to make a difference.

Good luck and have fun!