Re: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure

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.tes

Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure

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.