Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B81546339FE for ; Wed, 17 Jun 2009 17:39:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 58601-01 for ; Wed, 17 Jun 2009 17:39:19 -0300 (ADT) X-Greylist: delayed 00:09:33.42025 by SQLgrey-1.7.6 Received: from exprod5og114.obsmtp.com (exprod5og114.obsmtp.com [64.18.0.28]) by mail.postgresql.org (Postfix) with SMTP id BD3F6633583 for ; Wed, 17 Jun 2009 17:39:18 -0300 (ADT) Received: from source ([216.41.21.131]) by exprod5ob114.postini.com ([64.18.4.12]) with SMTP ID DSNKSjlUdbjbkGCCG9Ybnb5Dkz19l1/xBeYh@postini.com; Wed, 17 Jun 2009 13:39:18 PDT Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C9EF89.ED910212" X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Trapping statement timeout Date: Wed, 17 Jun 2009 16:26:43 -0400 Message-ID: <56121B05583DAD418E8EC20B9FC70C1BEFD5@mail-01.wrcapital.corp> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Trapping statement timeout Thread-Index: AcnvilkAICn6ONIHRYiocZFvTMGloA== From: "Kevin Duffy" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200906/85 X-Sequence-Number: 32690 This is a multi-part message in MIME format. ------_=_NextPart_001_01C9EF89.ED910212 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello All: =20 I certain that for you guys that this will be trivial. =20 What I need to do is to trap, go into an exception block, when a statement times out. =20 Let me explain. =20 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. =20 I foresee the situation where multiple users might kick off a run for the same data at the same time. =20 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: =20 create or replace FUNCTION fof_run( fundkey_in integer date_in date) returns void as=20 =20 $body$ =20 DECLARE =20 rowcnt integer; =20 BEGIN BEGIN TRANSACTION; =20 =20 SET LOCAL STATEMENT_TIMEOUT =3D 5000 =20 select count(*)into rowcnt from fofrun where fi_parentkey =3D fundkey_in and date_ =3D date_in; =20 if rowcnt =3D 0 then=20 insert into fofrum (fi_parentkey , date_, start_) values( fund_key_in, date_in, now ); =20 end if; =20 =20 select * from fofrun=20 where fi_parentkey =3D fundkey_in and date_ =3D date_in FOR UPDATE; =20 =20 =20 SET LOCAL STATEMENT_TIMEOUT =3D 0 =20 =20 =20 =20 COMMIT; =20 EXCEPTION WHEN =20 =20 =20 =20 END; =20 =20 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 =20 =20 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. =20 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_ =20 =20 Thank you for your attention to this matter. =20 Kevin Duffy ------_=_NextPart_001_01C9EF89.ED910212 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D 5000

     =

      select count(*)into rowcnt  from fofrun where fi_parentkey =3D fundkey_in and date_ =3D = date_in;

 

      if rowcnt =3D 0 then =

        insert = into fofrum (fi_parentkey , date_, start_)

         =    values( fund_key_in, date_in, now );

         =

      end = if;

 

     =

      select * from fofrun =

         =   where fi_parentkey =3D fundkey_in and date_ =3D = date_in

         =   FOR UPDATE;

 

 

 

      SET LOCAL = STATEMENT_TIMEOUT =3D 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

------_=_NextPart_001_01C9EF89.ED910212--