{"id":7933,"date":"2016-05-18T14:44:22","date_gmt":"2016-05-18T12:44:22","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/"},"modified":"2016-05-18T14:44:22","modified_gmt":"2016-05-18T12:44:22","slug":"a-look-at-postgresql-9-6-killing-idle-transactions-automatically","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/","title":{"rendered":"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically"},"content":{"rendered":"<p>A common issue with databases: The guy at floor 2 did some work on the database and then rashly left for lunch because the lady on floor 1 is already waiting for him. Unfortunately he did not close his transaction and now his session is blocking others. In PostgreSQL this might be even worse as all <a href=\"https:\/\/en.wikipedia.org\/wiki\/Tuple\" target=\"_blank\" rel=\"noopener\">tuples<\/a> modified by this transaction are not available for vacuum until the transaction is either committed or aborted. In <a href=\"http:\/\/www.postgresql.org\/docs\/9.6\/static\/release-9-6.html\" target=\"_blank\" rel=\"noopener\">PostgreSQL 9.6<\/a> there will be a way to minimize this risk.<\/p>\n<p><!--more--><\/p>\n<p>As always with PostgreSQL: If someone wants to add something to PostgreSQL core it starts with a <a href=\"http:\/\/www.postgresql.org\/message-id\/flat\/56AE0D10.6060805@2ndquadrant.fr#56AE0D10.6060805@2ndquadrant.fr\" target=\"_blank\" rel=\"noopener\">mail tread<\/a>. The result was a new parameter called <a href=\"http:\/\/www.postgresql.org\/docs\/9.6\/static\/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT\" target=\"_blank\" rel=\"noopener\">idle_in_transaction_session_timeout<\/a>. Lets see how this works.<\/p>\n<p>The default value of idle_in_transaction_session_timeout is 0, which means disabled:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5432) [postgres] &gt; show idle_in_transaction_session_timeout;\n idle_in_transaction_session_timeout \n-------------------------------------\n 0\n(1 row)\n<\/pre>\n<p>I&#8217;ll set it to one minute&#8230;<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5432) [postgres] &gt; alter system set idle_in_transaction_session_timeout='1min';\nALTER SYSTEM\n<\/pre>\n<p>Lets create a user:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5432) [postgres] &gt; create user u login password 'u';\nCREATE ROLE\n<\/pre>\n<p>Using that user login and start a transaction without doing any further work:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@centos7:\/home\/postgres\/ [PG1] psql postgres u -W\nPassword for user u: \npsql (9.6beta1 dbi services build)\nType \"help\" for help.\n\n(u@[local]:5432) [postgres] &gt; begin;\nBEGIN\n<\/pre>\n<p>Btw: Being idle in transaction is reported in the process list, too:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5432) [postgres] &gt; ! ps -ef | grep idle\npostgres  2712  2702  0 08:12 ?        00:00:00 postgres: postgres postgres [local] idle\npostgres  3072  2702  0 08:14 ?        00:00:00 postgres: u postgres [local] idle in transaction\npostgres  3412  2711  0 08:17 pts\/0    00:00:00 sh -c ps -ef | grep idle\npostgres  3414  3412  0 08:17 pts\/0    00:00:00 grep idle\n<\/pre>\n<p>After one minute this is reported in the log file:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n2016-05-18 08:17:32.352 CEST - 1 - 3072 - [local] - u@postgres FATAL:  terminating connection due to idle-in-transaction timeout\n<\/pre>\n<p>An attempt to do anything in the session that user u opened results in:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(u@[local]:5432) [postgres] &gt; select 1;\nFATAL:  terminating connection due to idle-in-transaction timeout\nserver closed the connection unexpectedly\n        This probably means the server terminated abnormally\n        before or while processing the request.\nThe connection to the server was lost. Attempting reset: Succeeded.\n<\/pre>\n<p>Works well &#8230; no more killing sessions because guys head for lunch \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A common issue with databases: The guy at floor 2 did some work on the database and then rashly left for lunch because the lady on floor 1 is already waiting for him. Unfortunately he did not close his transaction and now his session is blocking others. In PostgreSQL this might be even worse as [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-7933","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically\" \/>\n<meta property=\"og:description\" content=\"A common issue with databases: The guy at floor 2 did some work on the database and then rashly left for lunch because the lady on floor 1 is already waiting for him. Unfortunately he did not close his transaction and now his session is blocking others. In PostgreSQL this might be even worse as [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-18T12:44:22+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically\",\"datePublished\":\"2016-05-18T12:44:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/\"},\"wordCount\":211,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/\",\"name\":\"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-05-18T12:44:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/","og_locale":"en_US","og_type":"article","og_title":"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically","og_description":"A common issue with databases: The guy at floor 2 did some work on the database and then rashly left for lunch because the lady on floor 1 is already waiting for him. Unfortunately he did not close his transaction and now his session is blocking others. In PostgreSQL this might be even worse as [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/","og_site_name":"dbi Blog","article_published_time":"2016-05-18T12:44:22+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically","datePublished":"2016-05-18T12:44:22+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/"},"wordCount":211,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/","url":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/","name":"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-05-18T12:44:22+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-killing-idle-transactions-automatically\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"A look at PostgreSQL 9.6 \u2013 Killing idle transactions automatically"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7933","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7933"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7933\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7933"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7933"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7933"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7933"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}