Subject: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure

From: "Dirschel, Steve" <[email-masked]>
Date: Tue, 14 May 2024 20:24:49 +0000

We have a custom procedure to add/drop partitions in Postgres.  It has a main FOR LOOP to find tables needing to be processed.  Inside that FOR LOOP there 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 where if there is a long running query against the partitioned table it will block the DETACH PARTITION command.  Then what happens app sessions trying to insert into the table get blocked by the session trying to detach the partition and the app gets into a bad state.  In testing I found if I use the CONCURRENTLY clause with DETACH PARTITION the detach partition command can still get blocked by a long running query but that does not block app sessions from inserting into the table.  So this is great.

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 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 describe?

Thanks in advance.

This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended 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

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 14 May 2024 22:45:21 +0200

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

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

Yeah, ouch.

> 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/