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

On Wed, 2009-11-25 at 16:49 -0700, Scott Marlowe wrote:
> On Wed, Nov 25, 2009 at 3:19 PM, David Kerr <dmk(at)mr-paradox(dot)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