{"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":"

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’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’ll walk through various other functions, PostgreSQL provides by default.<\/p>\n

<\/p>\n

If you want to set parameters at the session level, you’ll usually use the “set”<\/a> command, e.g.:<\/p>\n

\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
As this is on the session level, you’ll get back to the default setting, once you create a new session:<\/p>\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
Another possibility you have for this requirement, is to use the build-in set_config<\/a> function:<\/p>\n
\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
The third parameter specifies the scope, and in this case the scope is the transaction. As psql by default auto commits and you’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
\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
If you want to set a parameter for the scope of the whole session, just switch the third parameter:<\/p>\n
\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
The opposite of the set_config function is current_setting:<\/p>\n
\npostgres=# select current_setting('work_mem',false);\n current_setting \n-----------------\n 4MB\n(1 row)\n<\/pre>\n
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
\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
Depending on what behavior you want, you’ll either get NULL or an error message.<\/p>\n
You might ask yourself: Why do I need these functions when I can do the same with set<\/a> and show<\/a>? The answer is easy: Functions can easily be used in other functions or procedures:<\/p>\n
\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
This gives you the flexibility to ask for, and set parameters while you’re doing business logic in the database.<\/p>\n","protected":false},"excerpt":{"rendered":"
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’ll look at how you can use the build-in functions to set and get […]<\/p>\n","protected":false},"author":28,"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":"\nPostgreSQL: 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’ll look at how you can use the build-in functions to set and get […]\" \/>\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=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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 & 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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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’ll look at how you can use the build-in functions to set and get […]","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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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 & 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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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}]}}