{"id":13771,"date":"2020-03-25T07:02:22","date_gmt":"2020-03-25T06:02:22","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/"},"modified":"2020-03-25T07:02:22","modified_gmt":"2020-03-25T06:02:22","slug":"postgresql-message-levels","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/","title":{"rendered":"PostgreSQL message levels"},"content":{"rendered":"<p>When you start to write business logic in the database by using triggers or functions\/procedures you usually want to report messages to the user that runs your code or you want to include some debugging output for your own. In Oracle a lot of people use the <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/arpls\/DBMS_OUTPUT.html\" target=\"_blank\" rel=\"noopener noreferrer\">dbms_output<\/a> package to return simple messages to the user&#8217;s screen. In PostgreSQL you can do the same but of course the way it is implemented is not the same. There are basically two parameters that control how much output is returned to the client or to the server&#8217;s log file: <a href=\"https:\/\/www.postgresql.org\/docs\/current\/runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES\" target=\"_blank\" rel=\"noopener noreferrer\">client_min_messages<\/a> and <a href=\"https:\/\/www.postgresql.org\/docs\/current\/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES\" target=\"_blank\" rel=\"noopener noreferrer\">log_min_messages<\/a>. <\/p>\n<p><!--more--><\/p>\n<p>Let&#8217;s start with <a href=\"https:\/\/www.postgresql.org\/docs\/current\/runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES\" target=\"_blank\" rel=\"noopener noreferrer\">client_min_messages<\/a>: The default value of this parameter is &#8216;NOTICE&#8217; and these are the valid values you cant set:<\/p>\n<ul>\n<li>DEBUG5<\/li>\n<li>DEBUG4<\/li>\n<li>DEBUG3<\/li>\n<li>DEBUG2<\/li>\n<li>DEBUG1<\/li>\n<li>LOG<\/li>\n<li>NOTICE<\/li>\n<li>WARNING<\/li>\n<li>ERROR<\/li>\n<\/ul>\n<p>A given level excludes all the lower levels, e.g. &#8220;LOG&#8221; would exclude all the &#8220;DEBUG*&#8221; levels and the default &#8220;NOTICE&#8221; will exclude &#8220;LOG&#8221; as well. The DEBUG* levels are usually not required and if you check the PostgreSQL source code you&#8217;ll notice that the highest level of information (DEBUG5) is not used that often while lower levels are used more often:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@centos8pg:\/home\/postgres\/postgresql\/ [pg13] grep -r \"DEBUG5\" * | grep -v sgml | wc -l\n20\npostgres@centos8pg:\/home\/postgres\/postgresql\/ [pg13] grep -r \"DEBUG4\" * | grep -v sgml | wc -l\n78\npostgres@centos8pg:\/home\/postgres\/postgresql\/ [pg13] grep -r \"DEBUG3\" * | grep -v sgml | wc -l\n64\npostgres@centos8pg:\/home\/postgres\/postgresql\/ [pg13] grep -r \"DEBUG2\" * | grep -v sgml | wc -l\n236\npostgres@centos8pg:\/home\/postgres\/postgresql\/ [pg13] grep -r \"DEBUG1\" * | grep -v sgml | wc -l\n221\n<\/pre>\n<p>Setting the highest level would give a lot of information even for basic tasks:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# set client_min_messages = 'DEBUG5';\nDEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid\/subid\/cid: 0\/1\/0\nSET\npostgres=# create table tt1 ( a int );\nDEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid\/subid\/cid: 0\/1\/0\nDEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid\/subid\/cid: 559\/1\/1\nCREATE TABLE\npostgres=# create index ii1 on tt1 (a);\nDEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid\/subid\/cid: 0\/1\/0\nDEBUG:  building index \"ii1\" on table \"tt1\" serially\nDEBUG:  index \"ii1\" can safely use deduplication\nDEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid\/subid\/cid: 560\/1\/2\nCREATE INDEX\n<\/pre>\n<p>Lowering that to &#8220;DEBUG1&#8221; already reduces the amount of messages:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# set client_min_messages = 'DEBUG1';\nSET\npostgres=# create table tt2 ( a int );\nCREATE TABLE\npostgres=# create index ii2 on tt2 (a);\nDEBUG:  building index \"ii2\" on table \"tt2\" serially\nDEBUG:  index \"ii2\" can safely use deduplication\nCREATE INDEX\npostgres=# \n<\/pre>\n<p>You can use most of the levels to control how much information is given back by your code as well. Let&#8217;s assume we have a function like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create or replace function f_test_msg () returns void \npostgres-# as\npostgres-# $$\npostgres$# declare\npostgres$# begin\npostgres$#     raise debug 'This is a DEBUG message';\npostgres$# end;\npostgres$# $$ language plpgsql;\nCREATE FUNCTION\n<\/pre>\n<p>With the default setting of &#8216;NOTICE&#8217; for client_min_messages you would not see any output when you execute the function:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# show client_min_messages;\n client_min_messages \n---------------------\n notice\n(1 row)\n\npostgres=# select f_test_msg();\n f_test_msg \n------------\n \n(1 row)\n<\/pre>\n<p>Setting client_min_messages to the appropriate level will give you the output:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [4]\">\npostgres=# set client_min_messages = 'DEBUG';\nSET\npostgres=# select f_test_msg();\nDEBUG:  This is a DEBUG message\n f_test_msg \n------------\n \n(1 row)\n\npostgres=# \n<\/pre>\n<p>It does not really matter to with &#8220;DEBUG&#8221; level you set the parameter, you&#8217;ll get the &#8220;raise debug&#8221; for all those levels:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [4,13]\">\npostgres=# set client_min_messages = 'DEBUG1';\nSET\npostgres=# select f_test_msg();\nDEBUG:  This is a DEBUG message\n f_test_msg \n------------\n \n(1 row)\n\npostgres=# set client_min_messages = 'DEBUG2';\nSET\npostgres=# select f_test_msg();\nDEBUG:  This is a DEBUG message\n f_test_msg \n------------\n \n(1 row)\n\npostgres=# \n<\/pre>\n<p>In the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/plpgsql-errors-and-messages.html\" target=\"_blank\" rel=\"noopener noreferrer\">documentation of RAISE<\/a> the only DEBUG level is &#8220;DEBUG&#8221; anyway. Using this method you can easily control the amount of messages your code will return, e.g.:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create or replace function f_test () returns void \npostgres-# as\npostgres-# $$\npostgres$# declare\npostgres$# begin\npostgres$#     raise debug 'This is a DEBUG message';\npostgres$#     raise log 'This is a LOG message';\npostgres$#     raise notice 'This is an NOTICE message';\npostgres$#     raise warning 'This is a WARNING message';\npostgres$# end;\npostgres$# $$ language plpgsql;\nCREATE FUNCTION\n<\/pre>\n<p>Depending on you current setting of client_min_messages you&#8217;ll get more or less messages:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# set client_min_messages = 'WARNING';\nSET\npostgres=# select f_test();\nWARNING:  This is a WARNING message\n f_test \n--------\n \n(1 row)\n\npostgres=# set client_min_messages = 'NOTICE';\nSET\npostgres=# select f_test();\nNOTICE:  This is an NOTICE message\nWARNING:  This is a WARNING message\n f_test \n--------\n \n(1 row)\n\npostgres=# set client_min_messages = 'LOG';\nSET\npostgres=# select f_test();\nLOG:  This is a LOG message\nNOTICE:  This is an NOTICE message\nWARNING:  This is a WARNING message\n f_test \n--------\n \n(1 row)\n\npostgres=# set client_min_messages = 'DEBUG';\nSET\npostgres=# select f_test();\nDEBUG:  This is a DEBUG message\nLOG:  This is a LOG message\nNOTICE:  This is an NOTICE message\nWARNING:  This is a WARNING message\n f_test \n--------\n \n(1 row)\n<\/pre>\n<p>Use that wisely and you will easily be able to debug your code by just setting the correct level for the information you want to get.<\/p>\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/current\/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES\" target=\"_blank\" rel=\"noopener noreferrer\">log_min_messages<\/a> on the other side controls how much information is written to PostgreSQL&#8217;s log file. There are two more levels you can set than for client_min_messages but the logic is the same:<\/p>\n<ul>\n<li>DEBUG5<\/li>\n<li>DEBUG4<\/li>\n<li>DEBUG3<\/li>\n<li>DEBUG2<\/li>\n<li>DEBUG1<\/li>\n<li>INFO<\/li>\n<li>NOTICE<\/li>\n<li>WARNING<\/li>\n<li>ERROR<\/li>\n<li>LOG<\/li>\n<li>FATAL<\/li>\n<\/ul>\n<p>The default is &#8220;WARNING&#8221; so you might want to lower that if you need more information in the log file or you need to collect debugging information.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you start to write business logic in the database by using triggers or functions\/procedures you usually want to report messages to the user that runs your code or you want to include some debugging output for your own. In Oracle a lot of people use the dbms_output package to return simple messages to the [&hellip;]<\/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-13771","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 v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL message levels - 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-message-levels\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL message levels\" \/>\n<meta property=\"og:description\" content=\"When you start to write business logic in the database by using triggers or functions\/procedures you usually want to report messages to the user that runs your code or you want to include some debugging output for your own. In Oracle a lot of people use the dbms_output package to return simple messages to the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-25T06:02:22+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=\"5 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-message-levels\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-message-levels\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"PostgreSQL message levels\",\"datePublished\":\"2020-03-25T06:02:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-message-levels\\\/\"},\"wordCount\":463,\"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-message-levels\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-message-levels\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-message-levels\\\/\",\"name\":\"PostgreSQL message levels - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-03-25T06:02:22+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-message-levels\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-message-levels\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-message-levels\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL message levels\"}]},{\"@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 message levels - 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-message-levels\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL message levels","og_description":"When you start to write business logic in the database by using triggers or functions\/procedures you usually want to report messages to the user that runs your code or you want to include some debugging output for your own. In Oracle a lot of people use the dbms_output package to return simple messages to the [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/","og_site_name":"dbi Blog","article_published_time":"2020-03-25T06:02:22+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"PostgreSQL message levels","datePublished":"2020-03-25T06:02:22+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/"},"wordCount":463,"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-message-levels\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/","name":"PostgreSQL message levels - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-03-25T06:02:22+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-message-levels\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL message levels"}]},{"@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\/13771","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=13771"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13771\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13771"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13771"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13771"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13771"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}