update functions locking tables

On Mon, Aug 29, 2005 at 09:41:21PM -0300, Clodoaldo Pinto wrote:

26873 ? S 0:18 postgres: user database 127.0.0.1(38946) SELECT waiting
23973 ? S 0:22 postgres: user database 127.0.0.1(43396) SELECT waiting
31212 ? S 0:02 postgres: user database 127.0.0.1(60619) SELECT waiting
21601 ? S 0:00 postgres: user database 127.0.0.1(46770) SELECT waiting

I've been trying for 3 days to figure out what is happening to no
avail. What am i missing about transactions and MVCC? What could make
a plpgsql update function lock a table? The indexes are default btree.

In general, writers shouldn't block readers. Have you examined
pg_locks? Do you know exactly what the blocked queries are, or can
you find out from pg_stat_activity (stats_command_string must be
enabled)? Are you doing any explicit locking (LOCK statement)?

--
Michael Fuhr

Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes:

I've been trying for 3 days to figure out what is happening to no
avail. What am i missing about transactions and MVCC? What could make
a plpgsql update function lock a table?

What is the function doing to the table, exactly? DDL changes generally
take exclusive locks ...

regards, tom lane

2005/8/29, Michael Fuhr <mike@fuhr.org>:

In general, writers shouldn't block readers. Have you examined
pg_locks? Do you know exactly what the blocked queries are, or can
you find out from pg_stat_activity (stats_command_string must be
enabled)? Are you doing any explicit locking (LOCK statement)?

This is one of the blocked queries:
select count (*) from times_producao where pontos_0 - pontos_7 > 0;

These selects were done during the updating:

select * from pg_locks;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+---------------------+---------
1813938 | 1813868 | | 7040 | AccessShareLock | t
1813938 | 1813868 | | 7040 | RowExclusiveLock | t
1813938 | 1813868 | | 7040 | ShareLock | t
1813938 | 1813868 | | 7040 | AccessExclusiveLock | t
1813939 | 1813868 | | 7040 | AccessShareLock | t
1813939 | 1813868 | | 7040 | RowExclusiveLock | t
1813939 | 1813868 | | 7040 | ShareLock | t
1813939 | 1813868 | | 7040 | AccessExclusiveLock | t
1813914 | 1813868 | | 24012 | AccessShareLock | f
1813892 | 1813868 | | 7040 | AccessShareLock | t
1813892 | 1813868 | | 7040 | RowExclusiveLock | t
1813914 | 1813868 | | 7040 | AccessShareLock | t
1813914 | 1813868 | | 7040 | RowExclusiveLock | t
1813914 | 1813868 | | 7040 | ShareLock | t
1813914 | 1813868 | | 7040 | AccessExclusiveLock | t
1813896 | 1813868 | | 7040 | AccessShareLock | t
16839 | 1813868 | | 12751 | AccessShareLock | t
2314110 | 1813868 | | 26871 | AccessShareLock | f
1813914 | 1813868 | | 26844 | AccessShareLock | f
| | 288553 | 26844 | ExclusiveLock | t
| | 288561 | 24012 | ExclusiveLock | t
| | 288548 | 7040 | ExclusiveLock | t
| | 288558 | 26871 | ExclusiveLock | t
1813914 | 1813868 | | 31212 | AccessShareLock | f
2314110 | 1813868 | | 7040 | AccessShareLock | t
2314110 | 1813868 | | 7040 | RowExclusiveLock | t
2314110 | 1813868 | | 7040 | ShareLock | t
2314110 | 1813868 | | 7040 | AccessExclusiveLock | t
| | 288556 | 31212 | ExclusiveLock | t
| | 288562 | 12751 | ExclusiveLock | t
1813887 | 1813868 | | 7040 | AccessShareLock | t
2314112 | 1813868 | | 7040 | ShareLock | t
2314112 | 1813868 | | 7040 | AccessExclusiveLock | t
1813907 | 1813868 | | 7040 | AccessShareLock | t
1813911 | 1813868 | | 7040 | AccessShareLock | t
(35 rows)

select *
from pg_stat_user_tables as a
inner join pg_locks as b
on a.relid = b.relation
;

relid | schemaname | relname | seq_scan | seq_tup_read |
idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del |
relation | database | transaction | pid | mode |
granted
---------+------------+-------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+----------+----------+-------------+-------+---------------------+---------
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 24012 | AccessShareLock | f
1813892 | public | last_date | 0 | 0 |
| | 0 | 0 | 0 | 1813892
| 1813868 | | 7040 | AccessShareLock | t
1813892 | public | last_date | 0 | 0 |
| | 0 | 0 | 0 | 1813892
| 1813868 | | 7040 | RowExclusiveLock | t
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 7040 | AccessShareLock | t
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 7040 | RowExclusiveLock | t
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 7040 | ShareLock | t
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 7040 | AccessExclusiveLock | t
1813896 | public | times | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813896
| 1813868 | | 7040 | AccessShareLock | t
2314110 | public | times_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 2314110
| 1813868 | | 31383 | AccessShareLock | f
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 12351 | AccessShareLock | f
2314110 | public | times_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 2314110
| 1813868 | | 26871 | AccessShareLock | f
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 26844 | AccessShareLock | f
2314110 | public | times_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 2314110
| 1813868 | | 24021 | AccessShareLock | f
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 31212 | AccessShareLock | f
2314110 | public | times_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 2314110
| 1813868 | | 7040 | AccessShareLock | t
2314110 | public | times_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 2314110
| 1813868 | | 7040 | RowExclusiveLock | t
2314110 | public | times_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 2314110
| 1813868 | | 7040 | ShareLock | t
2314110 | public | times_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 2314110
| 1813868 | | 7040 | AccessExclusiveLock | t
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 26872 | AccessShareLock | f
1813887 | public | datas | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813887
| 1813868 | | 7040 | AccessShareLock | t
1813914 | public | usuarios_producao | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813914
| 1813868 | | 8875 | AccessShareLock | f
1813907 | public | usuarios | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813907
| 1813868 | | 7040 | AccessShareLock | t
1813911 | public | usuarios_indice | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 1813911
| 1813868 | | 7040 | AccessShareLock | t
(23 rows)

I had just enabled stats_command_string and in about 15 hours i will
be able to post pg_stat_activity.

Regards, Clodoaldo Pinto

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

On Tue, Aug 30, 2005 at 08:39:52AM -0300, Clodoaldo Pinto wrote:

begin;
select update_last_date();
truncate times_producao;

TRUNCATE acquires an AccessExclusiveLock, which conflicts with all
other lock types. Locks are held until the transaction completes,
so once this lock is acquired no other transactions will be able
to access the table until this transaction commits or rolls back.

DELETE is slower than TRUNCATE but it won't block readers in other
transactions.

--
Michael Fuhr

2005/8/30, Michael Fuhr <mike@fuhr.org>:

TRUNCATE acquires an AccessExclusiveLock, which conflicts with all
other lock types. Locks are held until the transaction completes,
so once this lock is acquired no other transactions will be able
to access the table until this transaction commits or rolls back.

DELETE is slower than TRUNCATE but it won't block readers in other
transactions.

I think it is of great help. I will change it and let you know what happened.

Regards, Clodoaldo Pinto

Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes:

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;

I think truncate takes a table lock.
Just change it to "delete from times_producao".

Also, if consider doing a "vacuum full" or "cluster" after the batch job to
clear up the free space (not in a large transaction). That will still take a
table lock but it may be a small enough downtime to be worth the speed
increase the rest of the day.

--
greg

30 Aug 2005 09:10:51 -0400, Greg Stark <gsstark@mit.edu>:

I think truncate takes a table lock.
Just change it to "delete from times_producao".

Thanks, i will try it.

Also, if consider doing a "vacuum full" or "cluster" after the batch job to
clear up the free space (not in a large transaction). That will still take a
table lock but it may be a small enough downtime to be worth the speed
increase the rest of the day.

I'm already doing a vacuum (not full) once a day.

A vacuum full or a cluster is totally out of reach since each take
about one hour. The biggest table is 170 million rows long.

Regards, Clodoaldo Pinto

On Tue, Aug 30, 2005 at 10:18:20AM -0300, Clodoaldo Pinto wrote:

30 Aug 2005 09:10:51 -0400, Greg Stark <gsstark@mit.edu>:

Also, if consider doing a "vacuum full" or "cluster" after the batch job to
clear up the free space (not in a large transaction). That will still take a
table lock but it may be a small enough downtime to be worth the speed
increase the rest of the day.

I'm already doing a vacuum (not full) once a day.

A vacuum full or a cluster is totally out of reach since each take
about one hour.

Even if you cluster/vacuum only the just-loaded table?

The biggest table is 170 million rows long.

I hope this is not the one you are loading daily ...

--
Alvaro Herrera <alvherre[]alvh.no-ip.org> Architect, www.EnterpriseDB.com
"El destino baraja y nosotros jugamos" (A. Schopenhauer)

Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes:

I'm already doing a vacuum (not full) once a day.

A vacuum full or a cluster is totally out of reach since each take
about one hour. The biggest table is 170 million rows long.

Well a regular vacuum will mark the free space for reuse. If you insert or
update any records the new ones will go into those spots. Make sure you set
the fsm_* parameters high enough to cover all the updates and inserts for the
entire day (or repeat the vacuum periodically even if there are no deletes or
updates going on to create more free space).

You should realize that what's going on here is that the old records are still
in your table, marked as deleted. So any sequential scan will take twice as
long as otherwise. I think even index scans could take twice as long too
depending on the distribution of values.

I'm not saying that's untenable. If all your queries are fast enough then
you're set and it's just a cost of having no downtime.

--
greg

2005/8/30, Alvaro Herrera <alvherre@alvh.no-ip.org>:

On Tue, Aug 30, 2005 at 10:18:20AM -0300, Clodoaldo Pinto wrote:

A vacuum full or a cluster is totally out of reach since each take
about one hour.

Even if you cluster/vacuum only the just-loaded table?

No, that would be much faster. The biggest just updated is about 600
thousand rows. I will consider it.

The biggest table is 170 million rows long.

I hope this is not the one you are loading daily ...

I load daily 8 times 700+ thousand rows.

30 Aug 2005 10:35:31 -0400, Greg Stark <gsstark@mit.edu>:

Well a regular vacuum will mark the free space for reuse. If you insert or
update any records the new ones will go into those spots. Make sure you set
the fsm_* parameters high enough to cover all the updates and inserts for the
entire day (or repeat the vacuum periodically even if there are no deletes or
updates going on to create more free space).

I will check those fsm_* parameters.

On Tue, Aug 30, 2005 at 08:13:15AM -0300, Clodoaldo Pinto wrote:

2005/8/29, Michael Fuhr <mike@fuhr.org>:

In general, writers shouldn't block readers. Have you examined
pg_locks? Do you know exactly what the blocked queries are, or can
you find out from pg_stat_activity (stats_command_string must be
enabled)? Are you doing any explicit locking (LOCK statement)?

This is one of the blocked queries:
select count (*) from times_producao where pontos_0 - pontos_7 > 0;

FWIW, that where clause might be more efficient as
WHERE pontos_0 > pontos_7. Some databases would be able to use indexes
to answer that (not sure if PostgreSQL could), plus it removes an
operator. It also seems to be cleaner code to me. :)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461

2005/8/30, Jim C. Nasby <jnasby@pervasive.com>:

FWIW, that where clause might be more efficient as
WHERE pontos_0 > pontos_7. Some databases would be able to use indexes
to answer that (not sure if PostgreSQL could), plus it removes an
operator. It also seems to be cleaner code to me. :)
--

Done, thanks.

Regards, Clodoaldo Pinto