Re: Prevent internal error at concurrent CREATE OR REPLACE FUNCTION

On Mon, Mar 31, 2025 at 7:22 PM Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
>
> On Mon, 31 Mar 2025 20:00:57 +0900
> Yugo Nagata <nagata(at)sraoss(dot)co(dot)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.
>

hi.

+ /* Lock the function so nobody else can do anything with it. */
+ LockDatabaseObject(ProcedureRelationId, oldproc->oid, 0, AccessExclusiveLock);
+
+ /*
+ * It is possible that by the time we acquire the lock on function,
+ * concurrent DDL has removed it. We can test this by checking the
+ * existence of function. We get the tuple again to avoid the risk
+ * of function definition getting changed.
+ */
+ oldtup = SearchSysCacheCopy3(PROCNAMEARGSNSP,
+ PointerGetDatum(procedureName),
+ PointerGetDatum(parameterTypes),
+ ObjectIdGetDatum(procNamespace));

we already called LockDatabaseObject, concurrent DDL can
not do DROP FUNCTION or ALTER FUNCTION.
so no need to call SearchSysCacheCopy3 again?

@@ -553,11 +575,13 @@ ProcedureCreate(const char *procedureName,
replaces[Anum_pg_proc_proowner - 1] = false;
replaces[Anum_pg_proc_proacl - 1] = false;

+
+
/* Okay, do it... */
no need to add these two new lines.