Re: pg primary key bug? - Mailing list pgsql-sql
| From | pginfo |
|---|---|
| Subject | Re: pg primary key bug? |
| Date | |
| Msg-id | 4214B466.20908@t1.unisoftbg.com Whole thread Raw |
| In response to | Re: pg primary key bug? (Richard_D_Levine@raytheon.com) |
| Responses |
Re: pg primary key bug?
(Michael Fuhr <mike@fuhr.org>)
Re: pg primary key bug? (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-sql |
<br /> Hi all,<br /><br /> Sorry for my post, but I think it is pg primary key bug.<br /><br /> It is secont time in
thatwe found the bug (see the replays for this message).<br /> We have many server with pg and use it over jdbc +
jboss.<br/><br /> I am not able to stop this server for long time, but I have dumped the problem table.<br /> It is
veryimportant to know if it is bug, because we have many server running pg + our ERP and continuing to install new.<br
/><br/> 01=# select * from a_constants_str order by constname;<br /> constname | fid | constvalue <br />
-----------+-----+------------<br/> AACCGRID | 0 | SOF_3<br /> ADARID | 0 | SOF_2<br /> AGRADID | 0 |
SOF_165<br/> AKLGRID | 0 | SOF_8<br /> AKLIID | 0 | SOF_3513<br /> AKLTYPID | 0 | SOF_3<br /> ANMGRID
| 0 | SOF_10<br /> ANOMID | 0 | SOF_747<br /> AOBLASTID | 0 | SOF_3<br /> ASETUPID | 0 | SOF_399<br />
ASLUID | 0 | SOF_17<br /> AUSERID | 0 | SOF_3<br /> DOCID | 0 | SOF_25658<br /> DOCPLAID | 0 |
SOF_19738<br/> DOCPLAID | 0 | SOF_19738<br /> DOCPOGPLA | 0 | SOF_24281<br /> DOCRID | 0 | SOF_184547<br
/> LOCAID | 0 | SOF_13<br /> NASTRF | 0 | SOF_1<br /> TDOCID | 0 | SOF_47<br /> TDOCRID | 0 |
SOF_2439<br/> (21 rows)<br /><br /> 01=# select * from a_constants_str where constname='DOCPLAID' ;<br /> constname |
fid| constvalue <br /> -----------+-----+------------<br /> DOCPLAID | 0 | SOF_19738<br /> DOCPLAID | 0 |
SOF_19738<br/> (2 rows)<br /><br /><br /> 01=# \d a_constants_str;<br /> Table "public.a_constants_str"<br />
Column | Type | Modifiers <br /> ------------+-----------------------+-----------<br />
constname | character varying(30) | not null<br /> fid | integer | not null<br /> constvalue |
charactervarying(30) | <br /> Indexes:<br /> "a_constants_str_pkey" primary key, btree (constname, fid)<br /><br
/><br/> regards,<br /> ivan<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><a
class="moz-txt-link-abbreviated"href="mailto:Richard_D_Levine@raytheon.com">Richard_D_Levine@raytheon.com</a> wrote:<br
/><blockquotecite="midOFEF67063C.F61988F3-ON05256F9D.005A91D6@ftw.us.ray.com" type="cite"><pre wrap="">There are not
enoughspaces 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
<<a class="moz-txt-link-abbreviated"
href="mailto:grzm@myrealbox.com">grzm@myrealbox.com</a> To: pginfo <a class="moz-txt-link-rfc2396E"
href="mailto:pginfo@t1.unisoftbg.com"><pginfo@t1.unisoftbg.com></a>
> cc: <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>
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:
</pre><blockquote type="cite"><pre wrap="">I am using pg 7.4.5 on RedHat AS 3.0. </pre></blockquote><pre wrap="">
</pre><blockquotetype="cite"><pre wrap="">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. </pre></blockquote><pre wrap="">
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 <a
class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>)
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to <a
class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>)
</pre></blockquote><br />