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