Re: Bug with concurrent CREATE OR REPLACE (?)

Daniil Davydov <3danissimo(at)gmail(dot)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