Re: update functions locking tables

2005/8/29, Tom Lane <tgl@sss.pgh.pa.us>:
>
> What is the function doing to the table, exactly?  DDL changes generally
> take exclusive locks ...

This is the transaction:

begin;
select update_last_date();
truncate times_producao;
select kstime(), insert_times_producao(), kstime();
select kstime(), update_ranking_times(), kstime();
truncate usuarios_producao;
select kstime(), insert_usuarios_producao(), kstime();
analyze usuarios_producao;
select kstime(), update_ranking_usuarios(), kstime();
select kstime(), update_ranking_usuarios_time(), kstime();
select kstime(), update_team_active_members(), kstime();
commit;

This is one of the functions:

CREATE OR REPLACE FUNCTION update_ranking_usuarios()
  RETURNS void AS
$BODY$declare
  linha record;
  rank integer;
begin
rank := 0;
for linha in
  select usuario
    from usuarios_producao
    order by pontos_0 desc, pontos_7 desc, pontos_24 desc
loop
  rank := rank + 1;
  update usuarios_producao
    set rank_0 = rank
    where usuario = linha.usuario
    ;
end loop;
-- ----------------------------------------------------------
rank := 0;
for linha in
  select usuario
    from usuarios_producao
    order by pontos_0 + (pontos_7 / 7) desc, pontos_0 desc
loop
  rank := rank + 1;
  update usuarios_producao
    set rank_24 = rank
    where usuario = linha.usuario
    ;
end loop;
-- ----------------------------------------------------------
rank := 0;
for linha in
  select usuario
    from usuarios_producao
    order by pontos_0 + pontos_7 desc, pontos_0 desc
loop
  rank := rank + 1;
  update usuarios_producao
    set rank_7 = rank
    where usuario = linha.usuario
    ;
end loop;
-- ----------------------------------------------------------
rank := 0;
for linha in
  select usuario
    from usuarios_producao
    order by pontos_0 + (pontos_7 * 30 / 7) desc, pontos_0 desc
loop
  rank := rank + 1;
  update usuarios_producao
    set rank_30 = rank
    where usuario = linha.usuario
    ;
end loop;
return;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE STRICT;

There is no DDL inside the functions.

Regards, Clodoaldo Pinto