Need the ability to call alter table detach partition concurrently inside a procedure

From: "Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)com>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Cc: "Wong, Kam Fook (TR Technology)" <kamfook(dot)wong(at)thomsonreuters(dot)com>
Subject: Need the ability to call alter table detach partition concurrently inside a procedure
Date: 2024-05-15 14:18:09
Message-ID: BL0PR03MB4001A83D4D86959C4A7E3C82FAEC2@BL0PR03MB4001.namprd03.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(at)lists(dot)postgresql(dot)org<mailto:pgsql-general(at)lists(dot)postgresql(dot)org> distribution list about any work-around to the error above and alvherre(at)alvh(dot)no-ip(dot)org<mailto:alvherre(at)alvh(dot)no-ip(dot)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

Browse pgsql-bugs by date

  From Date Subject
Next Message Dmitry Dolgov 2024-05-15 14:35:06 Re: BUG #18463: Possible bug in stored procedures with polymorphic OUT parameters
Previous Message Robert Haas 2024-05-15 13:41:45 Re: BUG #18362: unaccent rules and Old Greek text