From: | Clodoaldo Pinto <clodoaldo(dot)pinto(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-general postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: update functions locking tables |
Date: | 2005-08-30 11:39:52 |
Message-ID: | a595de7a05083004391064d584@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-08-30 12:06:56 | Re: temp_buffers |
Previous Message | Nigel Horne | 2005-08-30 11:34:27 | Cursor declaration |