Tracking progress of (plpgsql?) operations

From: k992637(at)yahoo(dot)com
To: pgsql-novice(at)postgresql(dot)org
Subject: Tracking progress of (plpgsql?) operations
Date: 2006-04-30 23:36:04
Message-ID: 1146440164.927122.207540@g10g2000cwb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello All

I am implementing a system in PostresQL but I am pretty new to db
design in general so I don't really know how the type thing I am trying
to achieve is done (or that it can be done) in the real world.
Basically I want to know (so I can tell the users etc) how much of an
operation has been performed.

To make it simple lets say I have two tables, A and B, and a result
table C. A user may select a range of values for A, and then for each
of those values a function is appiled with each value of B as a
parameter. The result is written to C. I would like to be able to say
to the user "15 out of 20 calculations performed" or some such.

The way I have implemented things so far is to perform the calculation
in a plpgsql stored proc, then have a wrapper stored proc around that
which calls the calculation for each unique pair of A & B. The user's
selection of A is passed to the outer proc and then values of B are
found and passed to the inner stored proc with each value of A.
However the transactional nature of stored procs means I cannot (as far
as I can tell) see the intermediate stage of processing. What I would
ideally like is to be able to update a log table which stored how many
values of A were selected and how many values of A have finished
calculating, updating this every 5 iterations or so. However I will
never see an intermediate state in this table due to transactions (is
this correct?).

Obviously I cannot and would not want to abandon transactions, but I
would like to know if it is possible to implement this type of
behaviour in a way that I have not considered. How are things like
this done in the database world (if at all) ? I am using 7.4 on debian
if that is relevant.

Thanks
David

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Samuel, Rowena 2006-05-01 14:37:09 Snapshot Copy of a Postgres DB
Previous Message Robert Wimmer 2006-04-30 06:59:49 Re: error handling