{"id":1965,"date":"2015-04-06T23:58:39","date_gmt":"2015-04-06T20:58:39","guid":{"rendered":"http:\/\/www.profelis.com.tr\/tr\/blog\/?p=1965"},"modified":"2023-02-01T10:58:13","modified_gmt":"2023-02-01T07:58:13","slug":"postgresql-101-bekleyen-sorgular","status":"publish","type":"post","link":"https:\/\/profelis.com.tr\/en\/2015\/04\/06\/postgresql-101-bekleyen-sorgular\/","title":{"rendered":"PostgreSQL 101: Pending Queries"},"content":{"rendered":"<div title=\"Page 209\">\n<div>\n<h2>PostgreSQL 101: Pending Queries<\/h2>\n<p><strong>pg_stat_activity<\/strong> whose view is a boolean field <strong>waiting<\/strong> has the column. The values of this column<\/p>\n<pre> SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query =<\/pre>\n<p>queries where the transaction is ongoing, and<\/p>\n<pre>current_timestamp - query_start &gt; '1 min';<\/pre>\n<p>when selected, it runs as queries waiting for a system lock to be released. To explain it another way; <strong>pg_stat_activity<\/strong> if its appearance <strong>waiting = TRUE<\/strong> If we filter as, in this case the results are returned with the first query above.<\/p>\n<h2>Who is blocking my query?<\/h2>\n<p>After learning that a query is blocked, we immediately want to find out who is blocking it. A query similar to the one below will give us the answer to this problem:<\/p>\n<\/div>\n<div class=\"page\" title=\"Page 211\">\n<div class=\"layoutArea\">\n<div class=\"column\">\n<pre>SELECT\n    w.current_query as waiting_query,\n    w.procpid as w_pid,\n    w.usename as w_user,\n    l.current_query as locking_query,\n    l.procpid as l_pid,\n    l.usename as l_user,\n    t.schemaname || '.' || t.relname as tablename\nFROM pg_stat_activity w\n    join pg_locks l1 on w.procpid = l1.pid and not l1.granted\n    join pg_locks l2 on l1.relation = l2.relation and l2.granted\n    join pg_stat_activity l on  l2.pid = l.procpid\n    join pg_stat_user_tables t on l1.relation = t.relid\nWHERE w.waiting;\n\n<\/pre>\n<p>The result of this query will show the process ID along with the process number, the user, and the current query, along with the blocking and blocked operators. The query result also shows the names of the schema and table that caused the block.<\/p>\n<p>Usernames are also a system view that <strong>pg_stat_user_tables<\/strong> is retrieved from a view via a join.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>pg_stat_activity g\u00f6r\u00fcn\u00fcm\u00fc boolean bir alan olan waiting s\u00fctununa sahiptir.\u00a0Bu s\u00fctunun de\u011ferleri olarak se\u00e7ildi\u011finde bir sistem kilidinin (lock) a\u00e7\u0131lmas\u0131n\u0131 bekleyen sorgular olarak \u00e7al\u0131\u015f\u0131r. Di\u011fer bir \u015fekilde a\u00e7\u0131klamaya \u00e7al\u0131\u015f\u0131rsak; pg_stat_activity g\u00f6r\u00fcn\u00fcm\u00fcn\u00fc e\u011fer waiting = TRUE olarak filtrelersek bu durumda yukar\u0131daki ilk sorgu ile sonu\u00e7lar getirilir.<\/p>","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,45],"tags":[48,60],"class_list":["post-1965","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-yazi","tag-enterprisedb","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1965","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/comments?post=1965"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1965\/revisions"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=1965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=1965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=1965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}