Re: serial type (text instead of integer) and duplicate keys - Mailing list pgsql-general

Thank you very much.

Here is the output:
SELECT oid,xmin,xmax,ctid,id FROM articles WHERE id=391;
   oid   |   xmin    |   xmax    |   ctid   | id
---------+-----------+-----------+----------+-----
 3032469 | 152691945 | 152886684 |  (104,6) | 391
 3032469 | 152886687 | 156995994 | (104,13) | 391
(2 rows)

My version is 7.4.1
And no, I haven't had crashes. And I am using this database for 4-5 years.

Some days ago, the partition where my data is was full (just a few
seconds), but all the system continued running fine. (This is the only
possible cause of corruption I've detected).


On Apr 12, 2005 2:00 PM, Richard Huxton <dev@archonet.com> wrote:
> 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
>


--
[ http://www.improveyourweb.com/ ]
web.log.about.web.development