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

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(at)archonet(dot)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