Re: Error when lock conflict on REPLACE function - Mailing list pgsql-bugs

On mån, 2010-03-15 at 14:13 -0700, Josh Berkus wrote:
> 1. Create a function.
> 2. In one session, start an explicit transaction.
> 3. Do a CREATE OR REPLACE on the same function, but do not commit.
> 4. Open a 2nd session, and an explicit transaction in that session.
> 5. Do a CREATE OR REPLACE on the same function in the 2nd session.
> 6. COMMIT the 2nd session.
> 7. COMMIT the 1st session.
> 8. You get:
>
> ERROR: duplicate key value violates unique constraint
> "pg_proc_proname_args_nsp_index"
> SQL state: 23505
>
> What should have happened: the 2nd replace should have succeeded.  Or it
> should have given a user-friendly error message.  Opinions?

There is a whole host of, one might even say a bottomless pit of,
concurrency bugs in schema changes that don't involve tables (because
you can lock tables, but not much else).  You should consider yourself
lucky that the schema is still consistent after the operation; it's easy
to do much worse.

I think as people go more "agile" with their database designs, we will
see increasing demand to fix these sort of things.  But right now, it
think it's pretty hopeless to expect this to work right.