Thread: Getting "duplicate key value violates unique constraint" on 2nd run of function.

Howdy all.

I've got a function that basically does this:

DELETE FROM test where id = $1
INSERT into test (id) values ($1);

id is the primay key, so it has to be unique.

First time I run it, works great.

If I run it again in the same session, I get
ERROR:  duplicate key value violates unique constraint "test_pkey"

If I log out and then log back in, it runs fine again (the first time).

Is there some setting for the function that I need to set to make this run correctly
every time?

Thanks

Dave

On Wed, Nov 25, 2009 at 3:19 PM, David Kerr <dmk@mr-paradox.net> wrote:
> Howdy all.
>
> I've got a function that basically does this:
>
> DELETE FROM test where id = $1
> INSERT into test (id) values ($1);

You're missing a semi-colon up there, is that a problem?

> id is the primay key, so it has to be unique.
>
> First time I run it, works great.
> If I run it again in the same session, I get
> ERROR:  duplicate key value violates unique constraint "test_pkey"
>
> If I log out and then log back in, it runs fine again (the first time).
>
> Is there some setting for the function that I need to set to make this run correctly
> every time?

This should just work.  Please post a more complete example of what's
happening (php code, queries something) that reproduces this problem
in a way I can just type it in and see it on my end.

On Wed, 2009-11-25 at 16:49 -0700, Scott Marlowe wrote:
> On Wed, Nov 25, 2009 at 3:19 PM, David Kerr <dmk@mr-paradox.net> wrote:
> > Howdy all.
> >
> > I've got a function that basically does this:
> >
> > DELETE FROM test where id = $1
> > INSERT into test (id) values ($1);
>
> You're missing a semi-colon up there, is that a problem?
naw, that was pseudo code, sorry. The real function is long, but not
complex.

> > id is the primay key, so it has to be unique.
> >
> > First time I run it, works great.
> > If I run it again in the same session, I get
> > ERROR:  duplicate key value violates unique constraint "test_pkey"
> >
> > If I log out and then log back in, it runs fine again (the first time).
> >
> > Is there some setting for the function that I need to set to make this run correctly
> > every time?
>
> This should just work.  Please post a more complete example of what's
> happening (php code, queries something) that reproduces this problem
> in a way I can just type it in and see it on my end.
>

So this is weird. I tested it probably 10 - 15 times before i posted
this. each time i got the same thing: run the function once, fine, twice
duplicate value error.

I was creating the function just by doing
psql -f <file> <database>

Just for fun, i went into psql and did \i <file>
and ran the function, and now suddenly it works.
(no change to the file)

What's also odd, is that i can't break it by dropping and re-creating it
via psql -f now.

On Monday, I'll drop the DB and see if i can reproduce it. I doubt that
the \i thing fixed it, I suspect something else was going on in the
background (like a vacuum). (It is a single user system though, so there
wasn't much going on.)

Thanks

Dave