Re: Prevent internal error at concurrent CREATE OR REPLACE FUNCTION

On Mon, 30 Jun 2025 18:32:47 +0700
Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:

> Hi,
>
> On Mon, Jun 30, 2025 at 3:47 PM Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
> >
> > On Fri, 27 Jun 2025 18:53:02 +0700
> > Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:
> > > This patch fixes postgres behavior if I first create a function and
> > > then try to CREATE OR REPLACE it in concurrent transactions.
> > > But if the function doesn't exist and I try to call CREATE OR REPLACE
> > > in concurrent transactions, I will get an error.
> > > I wrote about it in this thread [1] and Tom Lane said that this
> > > behavior is kinda expected.
> > > Just in case, I decided to mention it here anyway - perhaps you will
> > > have other thoughts on this matter.
> > >
> > > [1] https://www.postgresql.org/message-id/flat/CAJDiXghv2JF5zbLyyybokWKM%2B-GYsTG%2Bhw7xseLNgJOJwf0%2B8w%40mail.gmail.com
> >
> > I agree with Tom Lane that the behavior is expected, although it would be better
> > if the error message were more user-friendly. We could improve it by checking the
> > unique constraint before calling index_insert in CatalogIndexInsert.
> >
>
> As far as I understand, unique constraint checking is specific for
> each index access method.
> Thus, to implement the proposed idea, you will have to create a
> separate callback for check_unique function.
> It doesn't seem like a very neat solution, but there aren't many other
> options left.

I believe check_exclusion_or_unique_constraint() can be used independently of
a specific index access method.

> I would suggest intercepting the error (via PG_CATCH), and if it has
> the ERRCODE_UNIQUE_VIOLATION code, change the error message (more
> precisely, throw another error with the desired message).
> If we caught an error during the CatalogTupleInsert call, we can be
> sure that the problem is in concurrent execution, because before the
> insertion, we checked that such a tuple does not exist.
>
> What do you think? And in general, are you going to fix this behavior
> within this thread?

Initially, I wasn't planning to do so, but I gave it a try and wrote a
patch to fix the issue based on my idea.

I've attached the patch as 0004. Other patches 0001-0003 are not changed.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>