Re: update functions locking tables

2005/8/29, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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