{"id":16315,"date":"2021-05-04T05:18:31","date_gmt":"2021-05-04T03:18:31","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/"},"modified":"2021-05-04T05:18:31","modified_gmt":"2021-05-04T03:18:31","slug":"postgresql-set_config-and-current_setting","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/","title":{"rendered":"PostgreSQL: set_config and current_setting"},"content":{"rendered":"<p>PostgreSQL comes with a rich set of administrative functions for various use cases. There are functions to control parameters, there are functions for index maintenance, others are for recovery and or backup control and there is much more. In this post we&#8217;ll look at how you can use the build-in functions to set and get parameters without touching the configuration file (or setting parameters at the cluster, database and user level). In the posts that follow, we&#8217;ll walk through various other functions, PostgreSQL provides by default.<\/p>\n<p><!--more--><\/p>\n<p>If you want to set parameters at the session level, you&#8217;ll usually use the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-set.html\">&#8220;set&#8221;<\/a> command, e.g.:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# show work_mem;\n work_mem \n----------\n 4MB\n(1 row)\n\npostgres=# set work_mem='6MB';\nSET\npostgres=# show work_mem;\n work_mem \n----------\n 6MB\n(1 row)\n<\/pre>\n<p>As this is on the session level, you&#8217;ll get back to the default setting, once you create a new session:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# c postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\npostgres=# show work_mem;\n work_mem \n----------\n 4MB\n(1 row)\n<\/pre>\n<p>Another possibility you have for this requirement, is to use the build-in <a href=\"https:\/\/www.postgresql.org\/docs\/current\/functions-admin.html#FUNCTIONS-ADMIN-SET\" target=\"_blank\" rel=\"noopener\">set_config<\/a> function:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select set_config('work_mem','6MB',true);\n set_config \n------------\n 6MB\n(1 row)\n\npostgres=# show work_mem;\n work_mem \n----------\n 4MB\n(1 row)\n<\/pre>\n<p>The third parameter specifies the scope, and in this case the scope is the transaction. As psql by default auto commits and you&#8217;re not in a transaction, you fall back to the default immediately. Doing the same in an explicit transactions makes this more clear:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# begin;\nBEGIN\npostgres=*# select set_config('work_mem','6MB',true);\n set_config \n------------\n 6MB\n(1 row)\n\npostgres=*# show work_mem;\n work_mem \n----------\n 6MB\n(1 row)\n\npostgres=*# end;\nCOMMIT\npostgres=# show work_mem;\n work_mem \n----------\n 4MB\n(1 row)\n<\/pre>\n<p>If you want to set a parameter for the scope of the whole session, just switch the third parameter:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select set_config('work_mem','6MB',false);\n set_config \n------------\n 6MB\n(1 row)\n\npostgres=# show work_mem ;\n work_mem \n----------\n 6MB\n(1 row)\n\npostgres=# c postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\npostgres=# show work_mem ;\n work_mem \n----------\n 4MB\n(1 row)\n<\/pre>\n<p>The opposite of the set_config function is current_setting:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select current_setting('work_mem',false);\n current_setting \n-----------------\n 4MB\n(1 row)\n<\/pre>\n<p>The reason for the second parameter is, to give you choice what happens if you ask for a parameter that does not exist:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select current_setting('work_memX',false);\nERROR:  unrecognized configuration parameter \"work_memX\"\npostgres=# select current_setting('work_memX',true);\n current_setting \n-----------------\n \n(1 row)\n<\/pre>\n<p>Depending on what behavior you want, you&#8217;ll either get NULL or an error message.<\/p>\n<p>You might ask yourself: Why do I need these functions when I can do the same with <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-set.html\" target=\"_blank\" rel=\"noopener\">set<\/a> and <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-show.html\" target=\"_blank\" rel=\"noopener\">show<\/a>? The answer is easy: Functions can easily be used in other functions or procedures:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# ! cat a.sql\ncreate or replace function f1() returns text as\n$$\ndeclare\n  lv text;\nbegin\n  select current_setting('work_mem')\n    into lv;\n  return lv;\nend;\n$$ language plpgsql;\npostgres=# i a.sql\nCREATE FUNCTION\npostgres=# select f1();\n f1  \n-----\n 4MB\n(1 row)\n<\/pre>\n<p>This gives you the flexibility to ask for, and set parameters while you&#8217;re doing business logic in the database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL comes with a rich set of administrative functions for various use cases. There are functions to control parameters, there are functions for index maintenance, others are for recovery and or backup control and there is much more. In this post we&#8217;ll look at how you can use the build-in functions to set and get [&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-16315","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>PostgreSQL: set_config and current_setting - 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\/postgresql-set_config-and-current_setting\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL: set_config and current_setting\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL comes with a rich set of administrative functions for various use cases. There are functions to control parameters, there are functions for index maintenance, others are for recovery and or backup control and there is much more. In this post we&#8217;ll look at how you can use the build-in functions to set and get [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-05-04T03:18:31+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=\"3 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\/postgresql-set_config-and-current_setting\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL: set_config and current_setting\",\"datePublished\":\"2021-05-04T03:18:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/\"},\"wordCount\":316,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/\",\"name\":\"PostgreSQL: set_config and current_setting - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2021-05-04T03:18:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL: set_config and current_setting\"}]},{\"@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":"PostgreSQL: set_config and current_setting - 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\/postgresql-set_config-and-current_setting\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL: set_config and current_setting","og_description":"PostgreSQL comes with a rich set of administrative functions for various use cases. There are functions to control parameters, there are functions for index maintenance, others are for recovery and or backup control and there is much more. In this post we&#8217;ll look at how you can use the build-in functions to set and get [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/","og_site_name":"dbi Blog","article_published_time":"2021-05-04T03:18:31+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL: set_config and current_setting","datePublished":"2021-05-04T03:18:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/"},"wordCount":316,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/","name":"PostgreSQL: set_config and current_setting - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-05-04T03:18:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-set_config-and-current_setting\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL: set_config and current_setting"}]},{"@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\/16315","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=16315"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16315\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16315"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16315"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16315"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16315"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}