Re: Prevent internal error at concurrent CREATE OR REPLACE FUNCTION

Hi,

On Mon, Jun 30, 2025 at 3:47 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:
>
> On Fri, 27 Jun 2025 18:53:02 +0700
> Daniil Davydov <3danissimo@gmail.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 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?

P.S.
Thank you for updating the patch.

--
Best regards,
Daniil Davydov