Re: Error when lock conflict on REPLACE function

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Error when lock conflict on REPLACE function
Date: 2010-03-16 11:02:08
Message-ID: 1268737328.16792.7.camel@fsopti579.F-Secure.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2010-03-16 14:08:08 Re: update the same tuple in one command twice
Previous Message terry 2010-03-16 07:15:29 update the same tuple in one command twice