From: "Dirschel, Steve" <steve.dirschel@thomsonreuters.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure
Thread-Topic: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a
 procedure
Thread-Index: AdqmO1nnsI6aILnmRumBC+x3Yd/TFw==
Date: Tue, 14 May 2024 20:24:49 +0000
Message-ID:
 <BL0PR03MB40015668F50A4DDA59390AE5FAE32@BL0PR03MB4001.namprd03.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?uYQ05EwI9iKp3C2Agly/s6T2iKYswmdq0E1dabyOK1P8aDh14XU9nW4/W2il?=
 =?us-ascii?Q?e4HqCD2jFqCkicYWkisMFmvwVQJ2Qw5qNjwXlflqPQg//2mkYxkvWQTYATy1?=
 =?us-ascii?Q?FTzoMNokBhPzVfaQd8tFC8V12p5O5C/Km1oBZsAcuD4CYCS1azTeMouOpqe9?=
 =?us-ascii?Q?lrPvTD7HoncHNFjT7o3R6p0GwdAr8QsWJIx0+x3EShY0cGmfRzMOjCoqAWHO?=
 =?us-ascii?Q?+5mPZzAFcGwQuTQjImb4iNAupuA3h0UAePkoKp5RO2cfXEE/WzJMKB2X6TMq?=
 =?us-ascii?Q?Ve7YPdy0y001f5A4MN+rVCNnUuXDTtjvaE+yCIErTuQxGRglpO8wKpyz7IXW?=
 =?us-ascii?Q?RpQe0L+MdcQzmO5NqnqZzKWMzTrm3r7S9ni2H4wL7VIljvP82joua8tNu8wq?=
 =?us-ascii?Q?M9vwakqjdSEwMAlSCXXO+nX/KEWnm4WiOIdxst5/c/3xHb4pvT3q8IkxNQPs?=
 =?us-ascii?Q?NB1dyWqFzEYVdRZ2vhZZ4uFT8WNVDqWbQzY79HsGa0IN7dvihuFB3oBmP+iE?=
 =?us-ascii?Q?7VX3O0FNhNQZd35rg2W9DAHtSB15zIdOJ3UjM9psJZidANLEciy/nhG+sL26?=
 =?us-ascii?Q?3vYRkKMOWpO44safIUzK7aNsICqYUYgk5o1JAyEidUTmRbPYIM2lO8mUcfyk?=
 =?us-ascii?Q?z66nFqJTHG15XRDshGZeVzxxISeG5TIKoqwcNvZxzw0g/0owPWGbXvOTDAHX?=
 =?us-ascii?Q?cYb1YvL04SqGgnJkQKaM74gBT0aQ9Fjym/6Hp6Y6Ky0hSR832kfqEDdKNnFU?=
 =?us-ascii?Q?w4tO4n/Ms1Mox3p4W72mDhOOM/7Ju2az4kMiPV8IM7sbupDk+Y6QCyufIWeC?=
 =?us-ascii?Q?2rTD5zqF2b8jDWYIJ51R+5jlhC6BVkCRbA0htXqQgLiteh06wS5JXi0LcoxT?=
 =?us-ascii?Q?ZflIPqf/YU63xmAucn1Zl4jUNF7q3egLQR90DbB3hHoiNnZEV1amH+f5vqF3?=
 =?us-ascii?Q?V8XXJioear6/jPIrnaqL+MR73s2QBs1SPgXcGXzdQYZBEF6xbOifBLIbuut4?=
 =?us-ascii?Q?413eym0IkAoU5TSH8Zn9sbTSxon90QV2oxifzwmeqgiRzpFupehk3O6uSxp2?=
 =?us-ascii?Q?7zV8rJGAm92GRrOm5mC9KtVVNzCudQhhYR07r29Ohq/U7D9Clap6loMT9vop?=
 =?us-ascii?Q?rTC8169k42FQ2IkyZmXr8/ynyJoMj/5uVhRuNtKUaEH3l6NI2RBR8vWgIjwH?=
 =?us-ascii?Q?j2J7/c/4GZC39Djhl+LMH/IrZWRFEGzzM7C5PRjub0vTV0hkEdDI9ODK5ZvM?=
 =?us-ascii?Q?0hZNC+Yz3XkOM7Lvg0zuwZ04tjtIhe91b9lW2FTYAANKUhakrp03DBPnfGuw?=
 =?us-ascii?Q?ViNr8BGzumzeoJwQC3mYaQ24wcSKszf+1KCl67omYILx8c2qgZkc1nDZWGJ9?=
 =?us-ascii?Q?jIVUKuVLJ+oAxhrhy4jpvSSrL4jFjsXWS4kKFlYcRTRZYS6QKofy0XgvoHPk?=
 =?us-ascii?Q?FRMY/Q0sV+IqZpmmkyw5LYyebeV8O/QIRvNTPwBj46CMX/Pvd4WDd0/rTion?=
 =?us-ascii?Q?Ld8soh1jBaNRmAYO1Mw+eUHB3hqNuvmBVTQ20qVob2adMR06fXeQgLv5Li9b?=
 =?us-ascii?Q?qGB2NgBoHHecUhOS0irouFHPfALnYQioR83fOlIAWbAst31zTQoIl5gB9+0e?=
 =?us-ascii?Q?bA=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_BL0PR03MB40015668F50A4DDA59390AE5FAE32BL0PR03MB4001namp_"
MIME-Version: 1.0
X-OriginatorOrg: thomsonreuters.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BL0PR03MB4001.namprd03.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 495d3af2-ea1b-4382-b1bc-08dc7453e781
X-MS-Exchange-CrossTenant-originalarrivaltime: 14 May 2024 20:24:49.4757
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 62ccb864-6a1a-4b5d-8e1c-397dec1a8258
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: N/JlxduasmV1GwDCwVzS5IAsvaJOfNOmgf9una+2fcDLZpbTVnm8l/vt3Cd+d3voJBeve9EDEVjy54TEqrLsZgFVNIkk+c+ruXbGy6367RvXj9XwjXDy58TRYmCE/VCY
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CH2PR03MB5333
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/BL0PR03MB40015668F50A4DDA59390AE5FAE32%40BL0PR03MB4001.namprd03.prod.outlook.com>
Precedence: bulk

--_000_BL0PR03MB40015668F50A4DDA59390AE5FAE32BL0PR03MB4001namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

We have a custom procedure to add/drop partitions in Postgres.  It has a ma=
in FOR LOOP to find tables needing to be processed.  Inside that FOR LOOP t=
here is a BEGIN so if a single table gets an error we catch the error in an=
 exception.  At the end of the END for the FOR LOOP it issues a commit.  So=
 if there are 20 tables to process and the 15th table gets an error it will=
 capture that error, write a message to a log file, and continue processing=
 the remaining tables.

We have ran into some locking issues when trying to DETACH a partition wher=
e if there is a long running query against the partitioned table it will bl=
ock the DETACH PARTITION command.  Then what happens app sessions trying to=
 insert into the table get blocked by the session trying to detach the part=
ition and the app gets into a bad state.  In testing I found if I use the C=
ONCURRENTLY clause with DETACH PARTITION the detach partition command can s=
till get blocked by a long running query but that does not block app sessio=
ns from inserting into the table.  So this is great.

But when I try and run the command inside the procedure it throws this erro=
r:

STATE: 25001 MESSAGE: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside=
 a transaction block DETAIL:  HINT:  CONTEXT: SQL statement "alter table t2=
.test1 detach partition t2.test1_gentime_20240511 concurrently"
PL/pgSQL function part.partition_maintenance() line 323 at EXECUTE

The documentation states:

CONCURRENTLY cannot be run in a transaction block and is not allowed if the=
 partitioned table contains a default partition.

Is there an option to call that CONCURRENTLY inside a procedure as I descri=
be?

Thanks in advance.

This e-mail is for the sole use of the intended recipient and contains info=
rmation that may be privileged and/or confidential. If you are not an inten=
ded recipient, please notify the sender by return e-mail and delete this e-=
mail and any attachments. Certain required legal entity disclosures can be =
accessed on our website: https://www.thomsonreuters.com/en/resources/disclo=
sures.html

--_000_BL0PR03MB40015668F50A4DDA59390AE5FAE32BL0PR03MB4001namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:"Open Sans";}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-ligatures:standardcontextual;}
code
	{mso-style-priority:99;
	font-family:"Courier New";}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">We have a custom procedure to add/drop partitions in=
 Postgres.&nbsp; It has a main FOR LOOP to find tables needing to be proces=
sed.&nbsp; Inside that FOR LOOP there is a BEGIN so if a single table gets =
an error we catch the error in an exception.&nbsp;
 At the end of the END for the FOR LOOP it issues a commit.&nbsp; So if the=
re are 20 tables to process and the 15<sup>th</sup> table gets an error it =
will capture that error, write a message to a log file, and continue proces=
sing the remaining tables.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">We have ran into some locking issues when trying to =
DETACH a partition where if there is a long running query against the parti=
tioned table it will block the DETACH PARTITION command.&nbsp; Then what ha=
ppens app sessions trying to insert into
 the table get blocked by the session trying to detach the partition and th=
e app gets into a bad state.&nbsp; In testing I found if I use the CONCURRE=
NTLY clause with DETACH PARTITION the detach partition command can still ge=
t blocked by a long running query but
 that does not block app sessions from inserting into the table.&nbsp; So t=
his is great.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">But when I try and run the command inside the proced=
ure it throws this error:<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">STATE: 25001 MESSAGE: ALTER TABLE ... DETACH CONCURR=
ENTLY cannot run inside a transaction block DETAIL:&nbsp; HINT:&nbsp; CONTE=
XT: SQL statement &quot;alter table t2.test1 detach partition t2.test1_gent=
ime_20240511 concurrently&quot;<o:p></o:p></p>
<p class=3D"MsoNormal">PL/pgSQL function part.partition_maintenance() line =
323 at EXECUTE<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">The documentation states:<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal"><code><span style=3D"font-size:10.0pt;color:black">C=
ONCURRENTLY</span></code><span style=3D"font-family:&quot;Open Sans&quot;,s=
ans-serif;color:black">&nbsp;cannot be run in a transaction block and is no=
t allowed if the partitioned table contains a default
 partition.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Open Sans&quot;,san=
s-serif;color:black"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal">Is there an option to call that CONCURRENTLY inside =
a procedure as I describe?&nbsp;
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Thanks in advance.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
This e-mail is for the sole use of the intended recipient and contains info=
rmation that may be privileged and/or confidential. If you are not an inten=
ded recipient, please notify the sender by return e-mail and delete this e-=
mail and any attachments. Certain
 required legal entity disclosures can be accessed on our website: https://=
www.thomsonreuters.com/en/resources/disclosures.html
</body>
</html>

--_000_BL0PR03MB40015668F50A4DDA59390AE5FAE32BL0PR03MB4001namp_--