public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alvaro Herrera <[email protected]>
To: Dirschel, Steve <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure
Date: Tue, 14 May 2024 22:45:21 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <BL0PR03MB40015668F50A4DDA59390AE5FAE32@BL0PR03MB4001.namprd03.prod.outlook.com>

On 2024-May-14, Dirschel, Steve wrote:

> But when I try and run the command inside the procedure it throws this error:
> 
> STATE: 25001
> MESSAGE: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block
> CONTEXT: SQL statement "alter table t2.test1 detach partition t2.test1_gentime_20240511 concurrently"
> PL/pgSQL function part.partition_maintenance() line 323 at EXECUTE

Yeah, ouch.

> The documentation states:
> 
> CONCURRENTLY cannot be run in a transaction block and is not allowed if the partitioned table contains a default partition.

Right.

> Is there an option to call that CONCURRENTLY inside a procedure as I describe?

Not at the moment.  The issue is that CONCURRENTLY needs to commit a
transaction internally and start a new one, and to ensure that works
correctly we check that it's being executed as a "top-level command",
which rules out procedures.  It may be possible to relax that
restriction when run inside procedures, given that procedures need
transaction control of their own anyway so we could arrange for the
right things to happen; but this is hypothetical and I don't know that
anybody has attempted to implement that.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox