Bug with concurrent CREATE OR REPLACE (?)

Hi

On 26.06.25 17:08, Daniil Davydov wrote:

Hi,
I found that this command sequence leads to an error. :

session 1:
begin;
create or replace function *some funcion*;

session 2:
begin;
create or replace function *same function as above*;

session 1:
commit;

session 2:
ERROR: duplicate key value violates unique constraint
"pg_proc_proname_args_nsp_index"

Error causes, because both transactions check catcache, decide that
function doesn't exist and then try to insert appropriate tuples into
pg_proc and its index (which has a unique constraint).
I found thread [1] in which Robert Haas said that this behaviour is
wrong, but nobody has enough time to fix it.

My question is, can we consider this behavior to be a mistake or a
normal occurrence?

[1] /messages/by-id/4B9EA2FD.8090902@agliodbs.com

Not entirely sure it's related (different error message), but a similar
problem was mentioned in this thread[1]

Best, Jim

1 -
/messages/by-id/20250331200057.00a62760966a821d484ea904@sraoss.co.jp

Daniil Davydov <3danissimo@gmail.com> writes:

I found that this command sequence leads to an error. :

session 1:
begin;
create or replace function *some funcion*;

session 2:
begin;
create or replace function *same function as above*;

session 1:
commit;

session 2:
ERROR: duplicate key value violates unique constraint
"pg_proc_proname_args_nsp_index"

This is operating as designed, more or less. The error message isn't
terribly user-friendly perhaps, but I think it's quite reasonable
to throw an error. Otherwise, which transaction's definition should
win out? The transactions are notionally operating at "the same
time", so saying that either the first-to-insert or the last-to-insert
ought to (silently) win isn't very satisfactory semantically.
Certainly, if you imagined that this were being done under
SERIALIZABLE transaction rules, you'd expect one of the transactions
to error out.

I actually think that the behavior is worse in the situation where
the function already existed: in that case both transactions try
to do an UPDATE, and one will fail with

ERROR: tuple concurrently updated

which is even less user-friendly. But again, this is about the
usefulness of the error message, not about whether we need to
avoid throwing any error.

In short: CREATE OR REPLACE is not a substitute for thinking about
how your application behaves. Why do you need to have multiple
transactions creating the same function at the same time?

regards, tom lane

Hi,

On Fri, Jun 27, 2025 at 12:05 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

This is operating as designed, more or less. The error message isn't
terribly user-friendly perhaps, but I think it's quite reasonable
to throw an error. Otherwise, which transaction's definition should
win out? The transactions are notionally operating at "the same
time", so saying that either the first-to-insert or the last-to-insert
ought to (silently) win isn't very satisfactory semantically.
Certainly, if you imagined that this were being done under
SERIALIZABLE transaction rules, you'd expect one of the transactions
to error out.

I actually think that the behavior is worse in the situation where
the function already existed: in that case both transactions try
to do an UPDATE, and one will fail with

ERROR: tuple concurrently updated

which is even less user-friendly. But again, this is about the
usefulness of the error message, not about whether we need to
avoid throwing any error.

OK, that sounds reasonable. Thanks!

In short: CREATE OR REPLACE is not a substitute for thinking about
how your application behaves. Why do you need to have multiple
transactions creating the same function at the same time?

Yep, I agree that this use case is bad, but some clients use such
logic and think that mentioned errors are a bug.
So I decided to clarify this moment.

--
Best regards,
Daniil Davydov