Thread: keeping track of function execution

keeping track of function execution

From
Wojtek
Date:
Hi,

I have a question on transactions/isolation levels/etc...
In my PL/pgSQL function main loop goes through inventory list of active
devices, for each one executing processing applicable for given device,
like:
FOR i in --i is %rowtype
select device_id as device_id,
type as type
from devices_list
where active = 1
LOOP
    (...)
-- here is CASE statement, checking value of 'type' parameter
   (....)
END LOOP; --simple enough, right?

This processing is pretty heavy and takes lot of time... so, I'd like to
be able to monitor as processing progresses and I need to be able to say:
-which devices've been processed already
-which ones've not been processed yet

My first idea was to create table, updated by my function each time next
device is processed, like:
device_id;status
1;0--done
2;0--done
3;1--processing is running
4;2--to be processed

But... Postgress treats function as single transaction, of course.
Hence, I'm not able to see any changes in my progress monitoring table
until my main function is finished and all the statuses are set to 0.
Which is not really the intent (again, the intent is to be able to
monitor which devices are yet to be processed while function is still
running!)

My ideas so far (none is perfect, unfortunately)
- move my loop to php/other external piece of code... so it will log-in
progress in my function using separate transactions (well, I don't want
to use external code, would prefer to stay in PL/pgSQL)
- log to text file (slow and not easy to report later on)

Can I ask for any other suggestions/comments? Is there a way I can have
this functionality, please?

Regards,
foo

Re: keeping track of function execution

From
"David Rowley"
Date:
Wojtek wrote:
> But... Postgress treats function as single transaction, of course.
> Hence, I'm not able to see any changes in my progress monitoring table
> until my main function is finished and all the statuses are set to 0.

You could use dblink() to insert into your logging table.

David.


Re: keeping track of function execution

From
justin
Date:
Wojtek wrote: <blockquote cite="mid:49FDBA8D.2030301@twine.pl" type="cite">Hi, <br /><br /> I have a question on
transactions/isolationlevels/etc... <br /> In my PL/pgSQL function main loop goes through inventory list of active
devices,for each one executing processing applicable for given device, like: <br /> FOR i in --i is %rowtype <br />
selectdevice_id as device_id, <br /> type as type <br /> from devices_list <br /> where active = 1 <br /> LOOP <br />
  (...) <br /> -- here is CASE statement, checking value of 'type' parameter <br />   (....) <br /> END LOOP; --simple
enough,right? <br /><br /> This processing is pretty heavy and takes lot of time... so, I'd like to be able to monitor
asprocessing progresses and I need to be able to say: <br /> -which devices've been processed already <br /> -which
ones'venot been processed yet <br /><br /> My first idea was to create table, updated by my function each time next
deviceis processed, like: <br /> device_id;status <br /> 1;0--done <br /> 2;0--done <br /> 3;1--processing is running
<br/> 4;2--to be processed <br /><br /> But... Postgress treats function as single transaction, of course. Hence, I'm
notable to see any changes in my progress monitoring table until my main function is finished and all the statuses are
setto 0. Which is not really the intent (again, the intent is to be able to monitor which devices are yet to be
processedwhile function is still running!) <br /><br /> My ideas so far (none is perfect, unfortunately) <br /> - move
myloop to php/other external piece of code... so it will log-in progress in my function using separate transactions
(well,I don't want to use external code, would prefer to stay in PL/pgSQL) <br /> - log to text file (slow and not easy
toreport later on) <br /><br /> Can I ask for any other suggestions/comments? Is there a way I can have this
functionality,please? <br /><br /> Regards, <br /> foo <br /><br /></blockquote><font face="Arial">What about using
Triggerswhen a device is being processed throw a flag in anther table or in the same table.  <br /><br /> I'm guessing
someother kind of process set the status 0, 1 or 2 before this slow process gets going.  if that is the case just add a
triggeron update to set the status.  Then a simple query  will get you what you want.<br /><br /> If  I'm understanding
whatyour after.<br /></font><br /> 

Re: keeping track of function execution

From
Scott Marlowe
Date:
On Sun, May 3, 2009 at 9:38 AM, Wojtek <foo@twine.pl> wrote:
> Hi,
>
> I have a question on transactions/isolation levels/etc...
> In my PL/pgSQL function main loop goes through inventory list of active
> devices, for each one executing processing applicable for given device,
> like:
> FOR i in --i is %rowtype
> select device_id as device_id,
> type as type
> from devices_list
> where active = 1
> LOOP
>   (...)
> -- here is CASE statement, checking value of 'type' parameter
>  (....)
> END LOOP; --simple enough, right?
>
> This processing is pretty heavy and takes lot of time... so, I'd like to be
> able to monitor as processing progresses and I need to be able to say:
> -which devices've been processed already
> -which ones've not been processed yet

You could raise notice and then tail the logs as it runs.  Or the
other option mentioned of using dblink() works well too.

Re: keeping track of function execution

From
Jasen Betts
Date:
On 2009-05-03, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Sun, May 3, 2009 at 9:38 AM, Wojtek <foo@twine.pl> wrote:
>> Hi,
>>
>> I have a question on transactions/isolation levels/etc...
>> In my PL/pgSQL function main loop goes through inventory list of active
>> devices, for each one executing processing applicable for given device,
>> like:
>> FOR i in --i is %rowtype
>> select device_id as device_id,
>> type as type
>> from devices_list
>> where active = 1
>> LOOP
>>   (...)
>> -- here is CASE statement, checking value of 'type' parameter
>>  (....)
>> END LOOP; --simple enough, right?
>>
>> This processing is pretty heavy and takes lot of time... so, I'd like to be
>> able to monitor as processing progresses and I need to be able to say:
>> -which devices've been processed already
>> -which ones've not been processed yet
>
> You could raise notice and then tail the logs as it runs.  Or the
> other option mentioned of using dblink() works well too.

you can retrieve notices through libpq. we're experimenting with using them,
and notifies to control the business logic in applications.

Re: keeping track of function execution

From
Wojtek
Date:
Scott Marlowe wrote:
> On Sun, May 3, 2009 at 9:38 AM, Wojtek <foo@twine.pl> wrote:
>
>> (...)
>>
>
> You could raise notice and then tail the logs as it runs.  Or the
> other option mentioned of using dblink() works well too.
>
dblink did the trick, thanks guys!

foo

PG_Bulkloader (or other tools) and partitions

From
Wojtek
Date:
Hi,

I'm looking for solution to speed up my data load process. Currently, I
have up to 100 insert/second loaded to partitioned table and application
is inserting data directly to partitions (not to master table).

As a solution, I wanted to explore pg_bulkloader but as I understand it
needs to have destination table specified in config file, so:
- I can't make it to write directly to partitions (as I need to write
data to several partitions every day)
- I doubt pg_bulkload will consider rules/constraints to put data to
right partitions automatically

Anyone can share good practices on loading data to partitioned table,
please? not necessarily using pg_bulkload...

Regards,
foo