3

I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with a very surprising conclusion: it looks like SELECTs are blocking SELECTs.

For example, the second report shows that this query:

SELECT infovalue from "info-field" where infotype ='xxxx'
                AND clientidnum = 146087240                 

is blocked by this query:

SELECT infotype FROM "info-field"                                           
                    WHERE infotype = 'yyyy'  
                    and refidnum = 0 LIMIT 1

And, even more surprising, that two instances of a single query are blocking each other:

SELECT "info-field".* FROM "info-field" where                             
    "info-field".infotype = 'www'                               
     and "info-field".infowho = 210745140 order by infoid desc LIMIT 1

and

SELECT "info-field".* FROM "info-field" where                                    
    "info-field".infotype = 'www'                               
     and "info-field".infowho = 229912040 order by infoid desc LIMIT 1

The high-level problem I'm looking into is that there are occasional bursts of activity where there are dozens of postgres processes spinning in sys time (so my guess is lock spinning, hence the investigation into locks).

My first issue is that I'm surprised by this apparent read-blocking-read problem. This is PostgreSQL 9.3.5. What could cause something like this?

The second is that the number of active processes from pg_stat_activity is ~~60, while the number of rows in the second report is ~~1900! How could such difference happen? There are no INSERT or UPDATE queries going on with the tables involved in SELECTs, and there are ~~5 INSERT or UPDATES queries running total in the reports.


Table details (on the related columns):

infotype text, 
infowho integer, 
clientidnum integer, 
refidnum integer, 
infoid integer serial primary key

Indexes:

(refidnum, infotype), 
(clientidnum, infotype), 
(infowho, infotype), 
(infoid),

partial index on 
  (refidnum, xfield, infotype) 
where 
  xfield in (1,2,3)
  • Can you provide the CREATE TABLE script for the table? – ypercubeᵀᴹ Oct 5 '15 at 13:10
  • 4
    I very much doubt that those select statements that you have shown really block each other. Do you maybe have an for update clause that you are not showing us? Are the sessions shown as "active" in pg_stat_activity? If they are shown as idle or idle in transaction that the query you are seeing there, is the last query the ran but the blocking might be cause by e.g. a DDL statement that was run earlier by the blocking session. – a_horse_with_no_name Oct 5 '15 at 13:15
  • @ypercube Not without extra bureaucracy, though I can describe its features: it's very simple, there are ~~20 fields, either text, integer or date, most are null-able, no additional constraints or foreign keys, and there are ~~10 btree indexes, about half of which are composite on infotype + another field. – Ivan Voras Oct 5 '15 at 13:16
  • @a_horse_with_no_name Those queries touching the info-field are active. There is no UPDATE / INSERT anywhere to be found near those queries (I mean, I grepped it just to be sure). – Ivan Voras Oct 5 '15 at 13:19
  • 3
    What isolation level are you using for your transactions? – jjanes Oct 5 '15 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.