Обсуждение: Bug with concurrent CREATE OR REPLACE (?)
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] https://www.postgresql.org/message-id/flat/4B9EA2FD.8090902%40agliodbs.com -- Best regards, Daniil Davydov
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] https://www.postgresql.org/message-id/flat/4B9EA2FD.8090902%40agliodbs.com Not entirely sure it's related (different error message), but a similar problem was mentioned in this thread[1] Best, Jim 1 - https://www.postgresql.org/message-id/flat/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