Re: keeping track of function execution
От | justin |
---|---|
Тема | Re: keeping track of function execution |
Дата | |
Msg-id | 49FDC9CE.8090100@emproshunts.com обсуждение исходный текст |
Ответ на | keeping track of function execution (Wojtek <foo@twine.pl>) |
Список | pgsql-general |
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 />
В списке pgsql-general по дате отправления: