X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7CE4F8B9F2F for ; Thu, 17 Feb 2005 16:07:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52323-10 for ; Thu, 17 Feb 2005 16:07:09 +0000 (GMT) Received: from unisoft.unisoftbg.com (unisoft.unisoftbg.com [194.12.229.193]) by svr1.postgresql.org (Postfix) with SMTP id 894E18B9FDE for ; Thu, 17 Feb 2005 16:07:07 +0000 (GMT) Received: (qmail 33299 invoked by uid 1011); 17 Feb 2005 16:10:23 -0000 Received: from pginfo@t1.unisoftbg.com by unisoft.unisoftbg.com by uid 1009 with qmail-scanner-1.22-st-qms (clamdscan: 0.75. spamassassin: 2.60. Clear:RC:0(192.168.1.201):SA:0(-4.8/5.0):. Processed in 16.279926 secs); 17 Feb 2005 16:10:23 -0000 X-Antivirus-MYDOMAIN-Mail-From: pginfo@t1.unisoftbg.com via unisoft.unisoftbg.com X-Antivirus-MYDOMAIN: 1.22-st-qms (Clear:RC:0(192.168.1.201):SA:0(-4.8/5.0):. Processed in 16.279926 secs Process 33291) Received: from unknown (HELO t1.unisoftbg.com) (pginfo@t1.unisoftbg.com@192.168.1.201) by unisoft.unisoftbg.com with SMTP; 17 Feb 2005 16:10:06 -0000 Message-ID: <4214B466.20908@t1.unisoftbg.com> Date: Thu, 17 Feb 2005 16:12:38 +0100 From: pginfo User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard_D_Levine@raytheon.com Cc: Michael Glaesemann , pgsql-sql@postgresql.org, pgsql-sql-owner@postgresql.org Subject: Re: pg primary key bug? References: Content-Type: multipart/alternative; boundary="------------080101030303090303060802" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.078 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_XBL X-Spam-Level: ** X-Archive-Number: 200502/190 X-Sequence-Number: 20301 --------------080101030303090303060802 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi all, Sorry for my post, but I think it is pg primary key bug. It is secont time in that we found the bug (see the replays for this message). We have many server with pg and use it over jdbc + jboss. I am not able to stop this server for long time, but I have dumped the problem table. It is very important to know if it is bug, because we have many server running pg + our ERP and continuing to install new. 01=# select * from a_constants_str order by constname; constname | fid | constvalue -----------+-----+------------ AACCGRID | 0 | SOF_3 ADARID | 0 | SOF_2 AGRADID | 0 | SOF_165 AKLGRID | 0 | SOF_8 AKLIID | 0 | SOF_3513 AKLTYPID | 0 | SOF_3 ANMGRID | 0 | SOF_10 ANOMID | 0 | SOF_747 AOBLASTID | 0 | SOF_3 ASETUPID | 0 | SOF_399 ASLUID | 0 | SOF_17 AUSERID | 0 | SOF_3 DOCID | 0 | SOF_25658 DOCPLAID | 0 | SOF_19738 DOCPLAID | 0 | SOF_19738 DOCPOGPLA | 0 | SOF_24281 DOCRID | 0 | SOF_184547 LOCAID | 0 | SOF_13 NASTRF | 0 | SOF_1 TDOCID | 0 | SOF_47 TDOCRID | 0 | SOF_2439 (21 rows) 01=# select * from a_constants_str where constname='DOCPLAID' ; constname | fid | constvalue -----------+-----+------------ DOCPLAID | 0 | SOF_19738 DOCPLAID | 0 | SOF_19738 (2 rows) 01=# \d a_constants_str; Table "public.a_constants_str" Column | Type | Modifiers ------------+-----------------------+----------- constname | character varying(30) | not null fid | integer | not null constvalue | character varying(30) | Indexes: "a_constants_str_pkey" primary key, btree (constname, fid) regards, ivan Richard_D_Levine@raytheon.com wrote: >There are not enough spaces available in the column to allow for the number >of DOC_IDs. There are three spaces, allowing for at most four occurrences >of DOC_ID, but there are eleven. --Rick > > > > Michael Glaesemann > > > cc: pgsql-sql@postgresql.org > Sent by: Subject: Re: [SQL] pg primary key bug? > pgsql-sql-owner@pos > tgresql.org > > > 02/03/2005 09:14 AM > > > > > > > >On Feb 3, 2005, at 21:53, pginfo wrote: > > > >>I am using pg 7.4.5 on RedHat AS 3.0. >> >> > > > >>sklad21=# \d a_constants_str >> Table "public.a_constants_str" >> Column | Type | Modifiers >>------------+-----------------------+----------- >>constname | character varying(30) | not null >>fid | integer | not null >>constvalue | character varying(30) | >>Indexes: >> "a_constants_str_pkey" primary key, btree (constname, fid) >> >>sklad21=# select * from a_constants_str ; >>constname | fid | constvalue -----------+-----+------------- >>AACCGRID | 0 | SOF_3 >>AKLTYPID | 0 | SOF_3 >>ADARID | 0 | SOF_2 >>AOBLASTID | 0 | SOF_6 >>AUSERID | 0 | SOF_17 >>ANMGRID | 0 | SOF_21 >>LOCAID | 0 | SOF_41 >>DOCID | 0 | SOF_1585254 >>DOCPLAID | 0 | SOF_1052900 >>AKLIID | 0 | SOF_18740 >>DOCRID | 0 | SOF_2268142 >>DOCPOGPLA | 0 | SOF_324586 >>DOCID | 0 | SOF_1585254 >>DOCID | 0 | SOF_1585254 >>DOCID | 0 | SOF_1585254 >>AKLGRID | 0 | SOF_45 >>DOCID | 0 | SOF_1585254 >>DOCID | 0 | SOF_1585254 >>ASETUPID | 0 | SOF_4605 >>DOCID | 0 | SOF_1585254 >>DOCID | 0 | SOF_1585254 >>TDOCID | 0 | SOF_337 >>TDOCRID | 0 | SOF_19450 >>DOCID | 0 | SOF_1585254 >>DOCID | 0 | SOF_1585254 >>AGRADID | 0 | SOF_256 >>DOCID | 0 | SOF_1585254 >>ASLUID | 0 | SOF_46 >>NASTRF | 0 | SOF_88 >>ANOMID | 0 | SOF_1200 >>(30 rows) >> >> >>Pls., see the records with 'DOCID' and note we have primary key >>defined. >> >> > > >It's unclear from just this data, but there's a chance that there are >varying numbers of spaces after 'DOCID', which would appear as separate >values for the index, even though they aren't readily apparent. Could >you show us the results of the following query? > >select constname, length(constname) as constname_length >from a_constants_str; > >Here's another one which would show if PostgreSQL is treating them >equally: > >select constname, count(constname) >from a_constants_str; > >The results of these queries might shed some light on the issue. > >Hope this helps. > >Michael Glaesemann >grzm myrealbox com > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > --------------080101030303090303060802 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
Hi all,

Sorry for my post, but I think it is pg primary key bug.

It is secont time in that we found the bug (see the replays for this message).
We have many server with pg and use it over jdbc + jboss.

I am not able to stop this server for long time, but I have dumped the problem table.
It is very important to know if it is bug, because we have many server running pg + our ERP and continuing to install new.

01=# select * from a_constants_str order by constname;
 constname | fid | constvalue
-----------+-----+------------
 AACCGRID  |   0 | SOF_3
 ADARID    |   0 | SOF_2
 AGRADID   |   0 | SOF_165
 AKLGRID   |   0 | SOF_8
 AKLIID    |   0 | SOF_3513
 AKLTYPID  |   0 | SOF_3
 ANMGRID   |   0 | SOF_10
 ANOMID    |   0 | SOF_747
 AOBLASTID |   0 | SOF_3
 ASETUPID  |   0 | SOF_399
 ASLUID    |   0 | SOF_17
 AUSERID   |   0 | SOF_3
 DOCID     |   0 | SOF_25658
 DOCPLAID  |   0 | SOF_19738
 DOCPLAID  |   0 | SOF_19738
 DOCPOGPLA |   0 | SOF_24281
 DOCRID    |   0 | SOF_184547
 LOCAID    |   0 | SOF_13
 NASTRF    |   0 | SOF_1
 TDOCID    |   0 | SOF_47
 TDOCRID   |   0 | SOF_2439
(21 rows)

01=# select * from a_constants_str where constname='DOCPLAID' ;
 constname | fid | constvalue
-----------+-----+------------
 DOCPLAID  |   0 | SOF_19738
 DOCPLAID  |   0 | SOF_19738
(2 rows)


01=# \d a_constants_str;
         Table "public.a_constants_str"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 constname  | character varying(30) | not null
 fid        | integer               | not null
 constvalue | character varying(30) |
Indexes:
    "a_constants_str_pkey" primary key, btree (constname, fid)


regards,
ivan









Richard_D_Levine@raytheon.com wrote:
There are not enough spaces available in the column to allow for the number
of DOC_IDs.  There are three spaces, allowing for at most four occurrences
of DOC_ID, but there are eleven.  --Rick


                                                                                                                                         
                      Michael Glaesemann                                                                                                 
                      <grzm@myrealbox.com        To:       pginfo <pginfo@t1.unisoftbg.com>                                              
                      >                          cc:       pgsql-sql@postgresql.org                                                      
                      Sent by:                   Subject:  Re: [SQL] pg primary key bug?                                                 
                      pgsql-sql-owner@pos                                                                                                
                      tgresql.org                                                                                                        
                                                                                                                                         
                                                                                                                                         
                      02/03/2005 09:14 AM                                                                                                
                                                                                                                                         
                                                                                                                                         





On Feb 3, 2005, at 21:53, pginfo wrote:

  
I am using pg 7.4.5 on RedHat AS 3.0.
    

  
sklad21=# \d a_constants_str
        Table "public.a_constants_str"
  Column   |         Type          | Modifiers
------------+-----------------------+-----------
constname  | character varying(30) | not null
fid        | integer               | not null
constvalue | character varying(30) |
Indexes:
   "a_constants_str_pkey" primary key, btree (constname, fid)

sklad21=# select * from a_constants_str ;
constname | fid | constvalue -----------+-----+-------------
AACCGRID  |   0 | SOF_3
AKLTYPID  |   0 | SOF_3
ADARID    |   0 | SOF_2
AOBLASTID |   0 | SOF_6
AUSERID   |   0 | SOF_17
ANMGRID   |   0 | SOF_21
LOCAID    |   0 | SOF_41
DOCID     |   0 | SOF_1585254
DOCPLAID  |   0 | SOF_1052900
AKLIID    |   0 | SOF_18740
DOCRID    |   0 | SOF_2268142
DOCPOGPLA |   0 | SOF_324586
DOCID     |   0 | SOF_1585254
DOCID     |   0 | SOF_1585254
DOCID     |   0 | SOF_1585254
AKLGRID   |   0 | SOF_45
DOCID     |   0 | SOF_1585254
DOCID     |   0 | SOF_1585254
ASETUPID  |   0 | SOF_4605
DOCID     |   0 | SOF_1585254
DOCID     |   0 | SOF_1585254
TDOCID    |   0 | SOF_337
TDOCRID   |   0 | SOF_19450
DOCID     |   0 | SOF_1585254
DOCID     |   0 | SOF_1585254
AGRADID   |   0 | SOF_256
DOCID     |   0 | SOF_1585254
ASLUID    |   0 | SOF_46
NASTRF    |   0 | SOF_88
ANOMID    |   0 | SOF_1200
(30 rows)


Pls., see the records with 'DOCID' and note we have primary key
defined.
    


It's unclear from just this data, but there's a chance that there are
varying numbers of spaces after 'DOCID', which would appear as separate
values for the index, even though they aren't readily apparent. Could
you show us the results of the following query?

select constname, length(constname) as constname_length
from a_constants_str;

Here's another one which would show if PostgreSQL is treating them
equally:

select constname, count(constname)
from a_constants_str;

The results of these queries might shed some light on the issue.

Hope this helps.

Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--------------080101030303090303060802--