Re: [HACKERS] Stored procedure issue - Mailing list pgsql-general

Hello

Here's the stored procedure itself,as well as the
related tables involved in it's calculations.
The idea for procedure is to find longest prefix match
for destination number,try to find it in table
'billing' for particular users,find the price,and
insert message into history and inqueue table,as well
as to decreace the user's balance in table 'users'.
Would it help to put all prefices,prices data in some
sort of cache and let procedure first try to match
with data from cache and if it can't find to try to
get data from table itself from hard disk ?

I'm looking for some solution where this procedure can
operate at higher loads and to leave other parts of
database operational as much as it could.

--Procedure---

create type dajbre as (status int,id bigint);

CREATE OR REPLACE FUNCTION
proc_uni(integer,integer,inet,text,integer,integer,text,integer,integer,
text,int, int,boolean,text) RETURNS setof dajbre AS '

DECLARE

uid alias for $1;
pid alias for $2;
ip_i alias for $3;
s_number alias for $4;
s_ton_i alias for $5;
s_npi_i alias for $6;
d_number alias for $7;
d_ton_i alias for $8;
d_npi_i alias for $9;
mess alias for $10;
dcs_i alias for $11;
esm_i alias for $12;
delivery_i alias for $13;
u_mess_id_i alias for $14;

r dajbre%rowtype;

prefixfound boolean;
prefixprice billing.price%TYPE;
dest_num_len int;
tmp_dest_number text;
tmp_user_bal numeric;
tmp_returnval int;
novi_status int;
tmp_his_id bigint;
tmp_u_mess_id_i text;


begin


dest_num_len := char_length(d_number);
tmp_dest_number := d_number;
prefixfound := false;


while dest_num_len > 0 loop

        select into prefixprice price from billing
        where u_id=uid and prefix=tmp_dest_number;

        if not found then
                tmp_dest_number := substring
(tmp_dest_number from 1 for dest_num_len-1);
                dest_num_len :=
char_length(tmp_dest_number);
        else
                prefixfound := true;
                exit;
        end if;
end loop;


if prefixfound=false then
        tmp_returnval :=11;
        novi_status :=11;
else if prefixprice = 0 then
        tmp_returnval :=11;
        novi_status :=50;
     else select into tmp_user_bal maxsms-cursms from
users where id=uid;
        if tmp_user_bal < prefixprice then
                tmp_returnval :=11;
                novi_status :=51;
        else
                tmp_returnval :=0;
        end if;
     end if;
end if;


if tmp_returnval = 0 then


insert into history
(ip,source,dest,message,dcs,esm,s_ton,s_npi,d_ton,d_npi,u_id,delivery,price,p_id,u_mess_id)
values

(ip_i,s_number,d_number,decode(mess,''base64''),dcs_i,esm_i,s_ton_i,s_npi_i,d_ton_i,d_npi_i,uid,delivery_i,prefixprice,pid,u_mess_id_i);

 tmp_his_id := currval(''history_id_seq'');



if pid = 2 then
        if u_mess_id_i = 0 then
                tmp_u_mess_id_i := '''';
        else
                tmp_u_mess_id_i := u_mess_id_i;
        end if;
else if pid = 3 then
          tmp_u_mess_id_i := tmp_his_id ;
     end if;
end if;

update history set u_mess_id = tmp_u_mess_id_i where
id = tmp_his_id;
update users set cursms=cursms+ prefixprice where
id=uid;


insert into inqueue(id, u_id) values (tmp_his_id,
uid);

r.status := 0;
r.id := tmp_his_id;
return next r;


else


        insert into rejected
(ip,source,dest,message,dcs,esm,s_ton,s_npi,d_ton,d_npi,status,u_id,delivery,u_mess_id)
values

(ip_i,s_number,d_number,decode(mess,''base64''),dcs_i,esm_i,s_ton_i,s_npi_i,d_ton_i,d_npi_i,novi_status,uid,delivery_i,u_mess_id_i);

r.status := 11;
r.id := 0;
return next r;

end if;

        return;

end;

' language 'plpgsql';
---------------------

---Billing table-----
                               Table "public.billing"


   Column   |      Type      |
Modifiers
------------+----------------+------------------------------------------------------
 id         | integer        | not null default
nextval('billing_id_seq'::regclass)
 u_id       | integer        | not null
 prefix     | text           |
 operator   | integer        |
 price      | numeric(20,10) |
 comment    | text           |
 new_prefix | boolean        | default false
Indexes:
    "billing_pkey" PRIMARY KEY, btree (id)
    "bil_uid" btree (u_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (u_id) REFERENCES users(id)
    "$2" FOREIGN KEY ("operator") REFERENCES
operators(id)
---------------------
----Users table------
       Column       |      Type      |
    Modifiers
--------------------+----------------+----------------------------------------------------
 id                 | integer        | not null
default nextval('users_id_seq'::regclass)
 username           | text           | not null
 password           | text           | not null
 name               | text           |
 email              | text           |
 mobile             | text           |
 phone              | text           |
 company            | text           |
 ownnum             | text           |
 reseller           | boolean        | default false
 reseller_id        | integer        | default 1
 url                | bytea          | not null
 maxsmpp            | smallint       | default 2
 maxucp             | smallint       | default 1
 http_enabled       | boolean        | default true
 smpp_enabled       | boolean        | default true
 ucp_enabled        | boolean        | default true
 enabled            | boolean        | default true
 comment            | text           |
 priority           | smallint       | default 1
 cursms             | numeric(20,10) | default 0
 maxsms             | numeric(20,10) | default 0
 address            | text           |
 fax                | text           |
 techname           | text           |
 techemail          | text           |
 techphone          | text           |
 finname            | text           |
 finemail           | text           |
 finphone           | text           |
 url_u              | text           |
 send_daily_balance | boolean        | default true
 currency           | integer        | default 1
 country            | integer        | default 0
 em_email           | text           |
 em_phone           | text           |
 log                | boolean        | default false
 postpay            | boolean        | default false
 sale_category      | text           |
 poen               | numeric(20,10) |
 commission         | numeric(20,10) |
 desktop            | boolean        | default false
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_username_key" UNIQUE, btree (username)
Foreign-key constraints:
    "users_sale_category_fkey" FOREIGN KEY
(sale_category) REFERENCES sale_categories(id)
-----------------------------

----Inqueue table------------
                Table "public.inqueue"
 Column |            Type             |   Modifiers
--------+-----------------------------+---------------
 id     | bigint                      | not null
 time   | timestamp without time zone | default now()
 u_id   | integer                     |
Indexes:
    "inqueue_date" btree ("time")
    "inqueue_idx" btree (id)
Foreign-key constraints:
    "$3" FOREIGN KEY (u_id) REFERENCES users(id)

--------------------------------

----------History table---------
                                         Table
"public.history"

  Column   |            Type             |
             Modifiers
-----------+-----------------------------+--------------------------------------------------------------
 id        | bigint                      | not null
default nextval(('history_id_seq'::text)::regclass)
 date      | date                        | default
now()
 time      | time without time zone      | default
now()
 source    | text                        | not null
 dest      | text                        | not null
 message   | bytea                       |
 dcs       | integer                     | default 0
 esm       | integer                     | default 0
 s_ton     | smallint                    | default 1
 s_npi     | smallint                    | default 1
 d_ton     | smallint                    | default 1
 d_npi     | smallint                    | default 1
 status    | integer                     | default -1
 u_id      | integer                     |
 mess_id   | text                        |
 d_date    | timestamp without time zone |
 provider  | integer                     | default -1
 delivery  | boolean                     | default
true
 p_id      | integer                     |
 msg_type  | integer                     | default 1
 ip        | inet                        |
 u_mess_id | text                        |
 priority  | smallint                    | default 2
 price     | numeric(20,10)              |
Indexes:
    "hist_pkey" PRIMARY KEY, btree (id)
    "hist_date" btree (date)
    "hist_dest" btree (dest)
    "hist_dr" btree (date, mess_id, provider)
    "hist_mess_id" btree (mess_id)
    "hist_uid_date" btree (u_id, date)
    "hist_users" btree (u_id)
Foreign-key constraints:
    "hist_msgtype" FOREIGN KEY (msg_type) REFERENCES
msg_type(id)
    "hist_pid" FOREIGN KEY (p_id) REFERENCES
protocols(id)
    "hist_provider" FOREIGN KEY (provider) REFERENCES
providers(id)
    "hist_uid1" FOREIGN KEY (u_id) REFERENCES
users(id)

--------------------------------
-----Rejected table--------------
                                 Table
"public.rejected"

  Column   |          Type          |
    Modifiers
-----------+------------------------+-----------------------------------------------------
 id        | bigint                 |
 date      | date                   | default
('now'::text)::date
 time      | time without time zone | default
('now'::text)::time(6) with time zone
 source    | text                   |
 dest      | text                   |
 message   | bytea                  |
 dcs       | integer                |
 esm       | integer                |
 s_ton     | smallint               |
 s_npi     | smallint               |
 d_ton     | smallint               |
 d_npi     | smallint               |
 status    | integer                |
 u_id      | integer                |
 delivery  | boolean                |
 p_id      | integer                |
 ip        | inet                   |
 u_mess_id | text                   |
 ajdi      | bigint                 | not null default
nextval('rejected_ajdi'::regclass)
Indexes:
    "rejected_pkey" PRIMARY KEY, btree (ajdi)
    "rejected_temp_date" btree (date)

---------------------------------

Sincerely

Pera

--- Usama Dar <munir.usama@gmail.com> wrote:

> On Dec 2, 2007 7:40 AM, Dragan Zubac
> <moroncic@yahoo.com> wrote:
>
> > Hello
> >
> > I have a stored procedure which does the billing
> stuff
> > in our system,it works ok,but if I put in
> > production,where there is some 5-10 billing events
> per
> > second,the whole database slows down. It won't
> even
> > drop some test table,reindex,vacuum,things which
> were
> > done before in the blink of an eye. If I stop the
> > application which calls the procedure,all is back
> to
> > normal.
> >
> > We didn't implement any special locking mechanism
> in
> > the procedure,all is default. The procedure is
> > updating user's balance in table 'users'. On the
> other
> > hand a couple of 'heavy load' table has foreign
> keys
> > pointing to table 'users'.
> >
> > Is it the matter of concurency and some locking
> issue
> > or maybe the existing of all those foreign keys
> > pointing to table 'users',or maybe something else
> > which we're not aware at the moment ?
>
>
> Can you please post your procedure and explain plan
> of the SQL which the
> procedure uses to do the billing stuff . There can
> be a zillion reasons for
> the performance problems you are seeing, but the
> email does not provide
> enough information.
>
>
> >
> > Sincerely
> >
> > Pera
> >
> >
> >
> >
>
____________________________________________________________________________________
> > Be a better sports nut!  Let your teams follow you
> > with Yahoo Mobile. Try it now.
> >
>
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project
> by donating at
> >
> >
> http://www.postgresql.org/about/donate
> >
>
>
>
> --
> Usama Munir Dar http://linkedin.com/in/usamadar
> Consultant Architect
> Cell:+92 321 5020666
> Skype: usamadar
>



      ____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how.  http://overview.mail.yahoo.com/