twoflower <standa.kurik@xxxxxxxxx> writes: > if I am reading the documentation on explicit locking > <http://www.postgresql.org/docs/current/interactive/explicit-locking.html#LOCKING-TABLES> > correctly, SELECT should never conflict with UPDATE. Pure SELECT, I would think not. But is it really a SELECT FOR UPDATE? That locks individual rows (not the whole table) so it can conflict against an UPDATE on the same row(s). > However, what I am > observing as a result of this monitoring query: > SELECT bl.pid AS blocked_pid, > a.usename AS blocked_user, > ka.query AS blocking_statement, > now() - ka.query_start AS blocking_duration, > kl.pid AS blocking_pid, > ka.usename AS blocking_user, > a.query AS blocked_statement, > now() - a.query_start AS blocked_duration > FROM pg_catalog.pg_locks bl > JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid > JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND > kl.pid != bl.pid > JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid > WHERE NOT bl.granted; Hmm. In any remotely modern version of PG, a pure SELECT transaction wouldn't even *have* a transactionid. So either the SELECT is a SELECT FOR UPDATE, or it's part of a transaction that's done data changes in the past. In that case the blockage could have something to do with previously-acquired locks. It's also possible that you're misreading the output of pg_locks. > 1) How is it possible that these two statements block? > 2) What can I do about it? EINSUFFICIENTDATA. You need to tell us more about the context, and show us the actual pg_locks query output. It might also be relevant just which PG version this is. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
