Re: serial type (text instead of integer) and duplicate

Carlos Costa wrote:
> Hello all!
>
> There is an extrange error in my logfile:
>
> update articles set online='t' where id = 391 ;
> ERROR: duplicate key violates unique constraint "articles_pkey"
>
> (the error exists only with this id)
>
> I've checked (well, almost imposible) if there was more than one
> article with this id:
>
> select count(*) from articles where id=391 ;
> count
> -------
> 1
> (1 row)
>
> Then, I checked the table:
>
> Table "public.articles"
> Column | Type | Modifiers
> -------------------+------------------------+-----------------------------------------------------
> id | integer | not null default
> nextval('"articles_id_seq"'::text)
>
> Here is the origin of my problem, I think: "text". "text"?. The
> "serial" type generate text instead of integer. Really extrange.

No - it's saying that 'articles_id_seq' is text. The sequence is
returning a number.

> So, my next query:
> select id from articles where id like '%391%' ;
> id
> -----
> 391
> 391
> (2 rows)
>
> The problem is easy to solve: delete and re-create the rows. But I
> would like to know the origin of this error. Any tip?

You're seeing two copies here because this query doesn't use the index
(you're forcing PG to convert id to text). You should be able to
recreate it using:
SET enable_indexscan=false;
SELECT * FORM articles WHERE id = 391;
In fact, you should do:
SET enable_indexscan=false;
SELECT oid,xmin,xmax,ctid,id FROM articles WEHRE id=391;
This will show some system columns too. If you post the results of this
query, I'm sure one of the developers will be able to identify the issue.

I'm guessing the unique index has been corrupted somehow. Two questions:
1. What version of PostgreSQL are you running?
2. Have you had any crashes?

If it is the index, a reindex or drop/recreate will solve it, but let's
see what's in the system columns first.
--
Richard Huxton
Archonet Ltd