Olá, estamos investigando o comportamento de uma aplicação que começou a apresentar erros após a migração do banco da versão 8.4 para a versão 9.2
Montamos a query abaixo (cruzamento de dados entre a pg_stat_activity e a pg_locks) e gostaríamos de ajuda para interpretar o resultado da mesma select a.pid, a.waiting, a.state, a.query, l.mode, count(*) from pg_stat_activity a, pg_locks l where a.pid =l.pid group by a.pid, a.waiting, a.state, a.query, l.mode order by 6 desc, a.pid; a.pid, a.waiting, a.state, a.query, l.mode, count(*) 43299;f;"idle";"COMMIT";"SIReadLock";37971 43327;f;"idle";"COMMIT";"SIReadLock";5055 43306;f;"idle";"COMMIT";"SIReadLock";730 43323;f;"idle";"COMMIT";"SIReadLock";191 43304;f;"idle";"COMMIT";"SIReadLock";24 Algumas sessões/processos estão com um número muito grande locks do tipo SIReadLock mesmo estando com o status=idle e tendo executado um COMMIT. A explicação para este comportamento seria o que está escrito no trecho abaixo do manual? There are two ways to acquire an advisory lock in PostgreSQL: at session level or at transaction level. Once acquired at session level, an advisory lock is held until explicitly released or the session ends. Unlike standard lock requests, session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later. A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released. http://www.postgresql.org/docs/9.2/static/explicit-locking.html#LOCKING-TABLES Grato, Equipe DBA IBGE
_______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
