Hi!
On 31.03.25 13:22, Yugo Nagata wrote:
> On Mon, 31 Mar 2025 20:00:57 +0900
> Yugo Nagata <nagata@sraoss.co.jp> wrote:
>
>> Hi,
>>
>> I found that multiple sessions concurrently execute CREATE OR REPLACE FUNCTION
>> for a same function, the error "tuple concurrently updated" is raised. This is
>> an internal error output by elog, also the message is not user-friendly.
>>
>> I've attached a patch to prevent this internal error by locking an exclusive
>> lock before the command and get the read tuple after acquiring the lock.
>> Also, if the function has been removed during the lock waiting, the new entry
>> is created.
> I also found the same error is raised when concurrent ALTER FUNCTION commands are
> executed. I've added a patch to fix this in the similar way.
>
> Regards,
> Yugo Nagata
I just briefly tested this patch and it seems to work as expected for
CREATE OF REPLACE FUNCTION:
-- Session 1 (t1):
postgres=# BEGIN;
BEGIN
postgres=*# CREATE OR REPLACE FUNCTION f1()
RETURNS INT LANGUAGE plpgsql AS
$$ BEGIN RETURN 1; END;$$;
CREATE FUNCTION
-- Session 2 (t2)
postgres=# CREATE OR REPLACE FUNCTION f1()
RETURNS INT LANGUAGE plpgsql AS
$$ BEGIN RETURN 2; END;$$;
(wait)
-- Session 3 (t3)
postgres=# CREATE OR REPLACE FUNCTION f1()
RETURNS INT LANGUAGE plpgsql AS
$$ BEGIN RETURN 3; END;$$;
(wait)
-- Session 4 (t4)
postgres=# CREATE OR REPLACE FUNCTION f1()
RETURNS INT LANGUAGE plpgsql AS
$$ BEGIN RETURN 4; END;$$;
CREATE FUNCTION
(wait)
-- Session 1 (t5)
postgres=*# END;
COMMIT
at this point Sessions 2, 3, and 4 were released with: CREATE FUNCTION
-- Session 1 (t6)
postgres=# \sf f1
CREATE OR REPLACE FUNCTION public.f1()
RETURNS integer
LANGUAGE plpgsql
AS $function$ BEGIN RETURN 4; END;$function$
So... it no longer shows the error message:
ERROR: tuple concurrently updated
I did the same for ALTER FUNCTION but I was unable to reproduce the
error your reported. Could you provide your script?
Best regards, Jim
