Thread: Error when lock conflict on REPLACE function

Severity: Annoyance
Versions Tested: 8.4.2
Platform: Linux RHEL 5.4
Reproduceable: always
Steps to reproduce:

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?

--Josh Berkus

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.

On Tue, Mar 16, 2010 at 7:02 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On m=E5n, 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. =A0Or =
it
>> should have given a user-friendly error message. =A0Opinions?
>
> 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). =A0You 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. =A0But right now, it
> think it's pretty hopeless to expect this to work right.

Perhaps this could be considered the leading edge of such demand...
we've had similar complaints before.  Unfortunately, it's unclear who
is willing to put in the time to fix it.

...Robert