Re: Long update progress

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

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(at)trade-india(dot)com>
To: <amarden(at)usa(dot)net>
Cc: <pgsql-admin(at)postgresql(dot)org>; <pgsql-general(at)postgresql(dot)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
> > 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/
>
>

"Andy Marden" <amarden(at)usa(dot)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