If I try to run alter table detach partition concurrently inside a procedure it throws error:
ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block
I posted a question to the pgsql-general@lists.postgresql.org distribution list about any work-around to the error above and alvherre@alvh.no-ip.org replied:
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.
Not sure if this person contributes to Postgres code but his reply sort of makes me think he has deeper knowledge of this and it seems it may be possible to allow this.
I’m not sure this is considered a bug but I searched around for a “postgres new features” e-mail and was unable to find anything so submitting this here.
Regards
Steve Dirschel
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