Long update progress

create a sequence and somehow involve it in a dummy manner in the update...

do a select current_val('sequence') to see how far updates have gone...

can we see the update stmt?

regds
mallah.

We have an database batch update process running. It runs normally and
takes around 6 hours. This is dealing with a much larger data set after
an error correction. It's been running for 6 days now and people are
getting twitchy that it might not finish. Is there any way (accepting
that more preparation would, in retrospect, have been better) to tell
how far we're got. This iterates round a cursor and updates individual
rows. The trouble is it commits once at the end.

The ideal would be to find a way of doing a dirty read against the
table that is bing updated. Then we'd know how many rows had been
processed.

Any ideas?

Cheers

Andy

---------------------------(end of
broadcast)--------------------------- TIP 5: Have you checked our
extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

The trouble is, the update's running at the moment - it's
actually a cursor with the updtae running for each selected row
in the cursor. If it's a case of running it again, I'll put a
commit every 'n' in there so that we see things. What I need to
be able to gauge is what has happened in the procedure so far.
Alternatively, if I could force it to abort, but commit what
it's done so far externally, then that would be fine too.

----- Original Message -----
From: <mallah@trade-india.com>
To: <amarden@usa.net>
Cc: <pgsql-admin@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Friday, July 19, 2002 1:46 PM
Subject: Re: [ADMIN] Long update progress

create a sequence and somehow involve it in a dummy manner in

the update...

do a select current_val('sequence') to see how far updates

have gone...

can we see the update stmt?

regds
mallah.

We have an database batch update process running. It runs

normally and

takes around 6 hours. This is dealing with a much larger

data set after

an error correction. It's been running for 6 days now and

people are

getting twitchy that it might not finish. Is there any way

(accepting

that more preparation would, in retrospect, have been

better) to tell

how far we're got. This iterates round a cursor and updates

individual

rows. The trouble is it commits once at the end.

The ideal would be to find a way of doing a dirty read

against the

table that is bing updated. Then we'd know how many rows had

been

processed.

Any ideas?

Cheers

Andy

---------------------------(end of
broadcast)--------------------------- TIP 5: Have you

checked our

"Andy Marden" <amarden@usa.net> writes:

We have an database batch update process running. It runs normally and takes
around 6 hours. This is dealing with a much larger data set after an error
correction. It's been running for 6 days now and people are getting twitchy
that it might not finish. Is there any way (accepting that more preparation
would, in retrospect, have been better) to tell how far we're got. This
iterates round a cursor and updates individual rows. The trouble is it
commits once at the end.

The ideal would be to find a way of doing a dirty read against the table
that is bing updated. Then we'd know how many rows had been processed.

A quick and dirty answer is just to watch the physical file for the
table being updated, and see how fast it's growing.

If you're using 7.2 then the contrib/pgstattuple function would let you
get more accurate info (note it will count not-yet-committed tuples as
"dead", which is a tad misleading, but at least it counts 'em).

regards, tom lane