Hello All:
I certain that for you guys that this will be trivial.
What I need to do is to trap, go into an exception block, when a statement times out.
Let me explain.
I have a processing that creates summary records based upon records in another table.
Think in terms of summary sales records for sales persons at multiple stores in multiple districts.
This process can run for a while and moves lots of data around.
I foresee the situation where multiple users might kick off a run for the same data at the same time.
So I would like to implement a locking mechanism where by if a district is being run, another user can not kick it off.
Please consider the following:
create or replace FUNCTION fof_run( fundkey_in integer date_in date)
returns void as
$body$
DECLARE
rowcnt integer;
BEGIN
BEGIN TRANSACTION;
SET LOCAL STATEMENT_TIMEOUT = 5000
select count(*)into rowcnt from fofrun where fi_parentkey = fundkey_in and date_ = date_in;
if rowcnt = 0 then
insert into fofrum (fi_parentkey , date_, start_)
values( fund_key_in, date_in, now );
end if;
select * from fofrun
where fi_parentkey = fundkey_in and date_ = date_in
FOR UPDATE;
SET LOCAL STATEMENT_TIMEOUT = 0
COMMIT;
EXCEPTION
WHEN
END;
The table FOFRUN will be the keymaster. So for a fund and a date if a records exists, lock it for update.
But only wait five seconds for the lock. If you get the lock, all is good.
If not I need to return a nice error message
Question:
When the SELECT FOR UPDATE fails/timeout what Error does it toss?
I looked in the PostgreSQL Error Codes and did not see one that matched.
When I am in the EXCEPTION block can I execute a normal SELECT against FOFRUN
to determine when the prior lock was obtained, based upon the value in start_
Thank you for your attention to this matter.
Kevin Duffy
