{"id":11035,"date":"2018-03-20T16:58:37","date_gmt":"2018-03-20T15:58:37","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/"},"modified":"2018-03-20T16:58:37","modified_gmt":"2018-03-20T15:58:37","slug":"postgresql-11-procedures-and-transaction-control","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/","title":{"rendered":"PostgreSQL 11: Procedures and transaction control"},"content":{"rendered":"

Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit<\/a>. On top of that there was another commit<\/a> which enables transaction control inside procedures which can be quite interesting. Lets see how that works.<\/p>\n

<\/p>\n

Up to PostgreSQL 10 the only choice to have something like a procedure is to create a function returning void:<\/p>\n

\npostgres=# select version();\n                                                          version                                                           \n----------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit\n(1 row)\n\npostgres=# CREATE FUNCTION dummy_func (id int) RETURNS VOID AS $$\npostgres$# DECLARE \npostgres$# BEGIN\npostgres$#   RAISE NOTICE 'id is %', id;\npostgres$# END;\npostgres$# $$  LANGUAGE plpgsql;\nCREATE FUNCTION\npostgres=# set client_min_messages = 'NOTICE';\nSET\npostgres=# select dummy_func(1);\nNOTICE:  id is 1\n dummy_func \n------------\n \n(1 row)\n<\/pre>\n
When you tried to do something like this it was not possible:<\/p>\n
\npostgres=# CREATE PROCEDURE dummy_proc (id int) AS $$\npostgres$# DECLARE \npostgres$# BEGIN\npostgres$#   raise notice 'id is %', id;\npostgres$# END;\npostgres$# $$  LANGUAGE plpgsql;\nERROR:  syntax error at or near \"PROCEDURE\"\nLINE 1: CREATE PROCEDURE dummy_proc (id int) AS $$\n<\/pre>\n
But now in the PostgreSQL development version you can do it (CREATE PROCEDURE<\/a>):<\/p>\n
\npostgres=# select version();\n                                                  version                                                   \n------------------------------------------------------------------------------------------------------------\n PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit\n(1 row)\n\npostgres=# CREATE PROCEDURE dummy_proc (id int) AS $$\npostgres$# DECLARE \npostgres$# BEGIN\npostgres$#   raise notice 'id is %', id;\npostgres$# END;\npostgres$# $$  LANGUAGE plpgsql;\nCREATE PROCEDURE\npostgres=# call dummy_proc(1);\nNOTICE:  id is 1\nCALL\n<\/pre>\n
Also notice that you need to use call<\/a> to execute a procedure. Using select as you would do it for a function will not work:<\/p>\n
\npostgres=# select dummy_proc(1);\nERROR:  dummy_proc(integer) is a procedure\nLINE 1: select dummy_proc(1);\n               ^\nHINT:  To call a procedure, use CALL.\n<\/pre>\n
On top of the commit which introduced procedures there was another one (see the beginning of this post) which introduced transaction control for procedures<\/a>. As the name implies this can be useful when you want to control transactions inside the procedure, e.g.:<\/p>\n
\npostgres=# create table t1 ( a int primary key );\nCREATE TABLE\npostgres=# CREATE OR REPLACE PROCEDURE dummy_proc2 (id int) AS $$\npostgres$# DECLARE \npostgres$#   l_id int := id;\npostgres$# BEGIN\npostgres$#   raise notice 'id is %', l_id;\npostgres$#   insert into t1 (a) values (l_id);\npostgres$#   commit;\npostgres$#   l_id := l_id + 1;\npostgres$#   raise notice 'id is %', l_id;\npostgres$#   insert into t1 (a) values (l_id);\npostgres$#   commit;\npostgres$#   raise notice 'id is %', l_id;\npostgres$#   insert into t1 (a) values (l_id);\npostgres$# END;\npostgres$# $$  LANGUAGE plpgsql;\n<\/pre>\n
When you execute this it will violate the primary key with the third insert:<\/p>\n
\npostgres=# call dummy_proc2(1);\nNOTICE:  id is 1\nNOTICE:  id is 2\nNOTICE:  id is 2\nERROR:  duplicate key value violates unique constraint \"t1_pkey\"\nDETAIL:  Key (a)=(2) already exists.\nCONTEXT:  SQL statement \"insert into t1 (a) values (l_id)\"\nPL\/pgSQL function dummy_proc2(integer) line 13 at SQL statement\n<\/pre>\n
… but because we can now commit (or rollback) in between we did not lose all the records:<\/p>\n
\npostgres=# select * from t1;\n a \n---\n 1\n 2\n(2 rows)\n<\/pre>\n
Trying to do the same with a function in PostgreSQL 10 will not work:<\/p>\n
\npostgres=# create table t1 ( a int primary key );\nCREATE TABLE\npostgres=# CREATE FUNCTION dummy_func2 (id int) RETURNS VOID AS $$\npostgres$# DECLARE \npostgres$#   l_id int := id;\npostgres$# BEGIN\npostgres$#   raise notice 'id is %', l_id;\npostgres$#   insert into t1 (a) values (l_id);\npostgres$#   commit;\npostgres$#   l_id := l_id + 1;\npostgres$#   raise notice 'id is %', l_id;\npostgres$#   insert into t1 (a) values (l_id);\npostgres$#   commit;\npostgres$#   raise notice 'id is %', l_id;\npostgres$#   insert into t1 (a) values (l_id);\npostgres$# END;\npostgres$# $$  LANGUAGE plpgsql;\nCREATE FUNCTION\npostgres=# select dummy_func2(1);\nNOTICE:  id is 1\nERROR:  cannot begin\/end transactions in PL\/pgSQL\nHINT:  Use a BEGIN block with an EXCEPTION clause instead.\nCONTEXT:  PL\/pgSQL function dummy_func2(integer) line 7 at SQL statement\npostgres=# select * from t1;\n a \n---\n(0 rows)\n<\/pre>\n
Nice features …<\/p>\n","protected":false},"excerpt":{"rendered":"
Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top […]<\/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":[586],"type_dbi":[],"class_list":["post-11035","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postegresql"],"acf":[],"yoast_head":"\nPostgreSQL 11: Procedures and transaction control - 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-11-procedures-and-transaction-control\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 11: Procedures and transaction control\" \/>\n<meta property=\"og:description\" content=\"Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-20T15:58:37+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-11-procedures-and-transaction-control\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 11: Procedures and transaction control\",\"datePublished\":\"2018-03-20T15:58:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/\"},\"wordCount\":238,\"commentCount\":0,\"keywords\":[\"PostegreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/\",\"name\":\"PostgreSQL 11: Procedures and transaction control - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-03-20T15:58:37+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 11: Procedures and transaction control\"}]},{\"@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 &\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 11: Procedures and transaction control - 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-11-procedures-and-transaction-control\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 11: Procedures and transaction control","og_description":"Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/","og_site_name":"dbi Blog","article_published_time":"2018-03-20T15:58:37+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-11-procedures-and-transaction-control\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 11: Procedures and transaction control","datePublished":"2018-03-20T15:58:37+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/"},"wordCount":238,"commentCount":0,"keywords":["PostegreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/","name":"PostgreSQL 11: Procedures and transaction control - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-03-20T15:58:37+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-11-procedures-and-transaction-control\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 11: Procedures and transaction control"}]},{"@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 &\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\/11035","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=11035"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11035\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11035"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11035"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11035"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11035"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}