Postgres Pro
Downloads
Home   >   mailing lists

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

serial type (text instead of integer) and duplicate keys

From
Carlos Costa
Date:
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.

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?


Thanks in advance,
Carlos

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

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

From
Richard Huxton
Date:
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

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

From
Carlos Costa
Date:
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

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

From
Tom Lane
Date:
Carlos Costa <ccosta@gmail.com> writes:
> 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)

Could we see cmin,cmax as well?

Since the OID is the same, these are evidently two versions of the same
row; somehow one of them didn't get marked dead when the other one was
created.  What patterns of updating do you use on this table?  Any
SELECT FOR UPDATE?

            regards, tom lane