Re: update functions locking tables

2005/8/29, Michael Fuhr <mike(at)fuhr(dot)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