pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpg primary key bug?
27+ messages / 8 participants
[nested] [flat]
* pg primary key bug?
@ 2005-02-03 12:53 pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 27+ messages in thread
From: pginfo @ 2005-02-03 12:53 UTC (permalink / raw)
To: pgsql-sql
Hi ,
I am using pg 7.4.5 on RedHat AS 3.0.
I am using it via jdbc and jboss.
I have found big problem about the way pg supports primary keys.
The bug was reported from my customers ( we are installed pg with our
ERP on ~ 500 costomers) and I do not know hot it is happen.
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.
At this moment we will create a new db and dump and reload the data.
The old one will stay and if needet we will be able to study the files
(the access to this customer is not very simple, but possible).
regards,
ivan.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-03 13:52 pginfo <pginfo@t1.unisoftbg.com>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 0 replies; 27+ messages in thread
From: pginfo @ 2005-02-03 13:52 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
Hi,
It is not spaces problem.
I needet to dump the data and I deleted all rows for 'DOCID' with
delete from a_constants_str where constname= 'DOCID'; and the pg
reported 10 rows deleted.
Sorry I can not execute the commend now ( I dropped the data, becaus I
needet the uniqu constnames for the restore).
regards,
ivan.
Michael Glaesemann wrote:
>
> 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
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-03 14:14 Michael Glaesemann <grzm@myrealbox.com>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 27+ messages in thread
From: Michael Glaesemann @ 2005-02-03 14:14 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-03 16:30 Richard_D_Levine@raytheon.com
0 siblings, 1 reply; 27+ messages in thread
From: Richard_D_Levine@raytheon.com @ 2005-02-03 16:30 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pginfo <pginfo@t1.unisoftbg.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
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)
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-17 15:12 pginfo <pginfo@t1.unisoftbg.com>
parent: Richard_D_Levine@raytheon.com
0 siblings, 2 replies; 27+ messages in thread
From: pginfo @ 2005-02-17 15:12 UTC (permalink / raw)
To: Richard_D_Levine@raytheon.com; +Cc: Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
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)
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-17 16:01 pginfo <pginfo@t1.unisoftbg.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 27+ messages in thread
From: pginfo @ 2005-02-17 16:01 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
Hi,
No, I do not inherited tables.
The result of this query is:
01=# SELECT tableoid::regclass, *
01-# FROM a_constants_str
01-# WHERE constname = 'DOCPLAID';
tableoid | constname | fid | constvalue
-----------------+-----------+-----+------------
a_constants_str | DOCPLAID | 0 | SOF_19738
a_constants_str | DOCPLAID | 0 | SOF_19738
(2 rows)
regards,
ivan.
Michael Fuhr wrote:
>On Thu, Feb 17, 2005 at 04:12:38PM +0100, pginfo wrote:
>
>
>
>>01=# select * from a_constants_str where constname='DOCPLAID' ;
>>constname | fid | constvalue
>>-----------+-----+------------
>>DOCPLAID | 0 | SOF_19738
>>DOCPLAID | 0 | SOF_19738
>>(2 rows)
>>
>>
>
>Do you have any inherited tables? What's the result of the following
>query?
>
>SELECT tableoid::regclass, *
>FROM a_constants_str
>WHERE constname = 'DOCPLAID';
>
>Inherited tables are documented to have deficiencies regarding
>constraints. Observe:
>
>CREATE TABLE parent (
> constname varchar(30) NOT NULL,
> fid integer NOT NULL,
> constvalue varchar(30),
> PRIMARY KEY (constname, fid)
>);
>
>CREATE TABLE child () INHERITS (parent);
>
>INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
>
>INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
>ERROR: duplicate key violates unique constraint "parent_pkey"
>
>INSERT INTO child VALUES ('DOCPLAID', 0, 'SOF_19738');
>
>SELECT tableoid::regclass, * FROM parent;
> tableoid | constname | fid | constvalue
>----------+-----------+-----+------------
> parent | DOCPLAID | 0 | SOF_19738
> child | DOCPLAID | 0 | SOF_19738
>(2 rows)
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-17 16:40 Michael Fuhr <mike@fuhr.org>
parent: pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 1 reply; 27+ messages in thread
From: Michael Fuhr @ 2005-02-17 16:40 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
On Thu, Feb 17, 2005 at 04:12:38PM +0100, pginfo wrote:
> 01=# select * from a_constants_str where constname='DOCPLAID' ;
> constname | fid | constvalue
> -----------+-----+------------
> DOCPLAID | 0 | SOF_19738
> DOCPLAID | 0 | SOF_19738
> (2 rows)
Do you have any inherited tables? What's the result of the following
query?
SELECT tableoid::regclass, *
FROM a_constants_str
WHERE constname = 'DOCPLAID';
Inherited tables are documented to have deficiencies regarding
constraints. Observe:
CREATE TABLE parent (
constname varchar(30) NOT NULL,
fid integer NOT NULL,
constvalue varchar(30),
PRIMARY KEY (constname, fid)
);
CREATE TABLE child () INHERITS (parent);
INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
ERROR: duplicate key violates unique constraint "parent_pkey"
INSERT INTO child VALUES ('DOCPLAID', 0, 'SOF_19738');
SELECT tableoid::regclass, * FROM parent;
tableoid | constname | fid | constvalue
----------+-----------+-----+------------
parent | DOCPLAID | 0 | SOF_19738
child | DOCPLAID | 0 | SOF_19738
(2 rows)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-17 16:41 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 27+ messages in thread
From: pginfo @ 2005-02-17 16:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
Hi,
Tom Lane wrote:
>pginfo <pginfo@t1.unisoftbg.com> writes:
>
>
>>01=# select * from a_constants_str where constname='DOCPLAID' ;
>> constname | fid | constvalue
>>-----------+-----+------------
>> DOCPLAID | 0 | SOF_19738
>> DOCPLAID | 0 | SOF_19738
>>(2 rows)
>>
>>
>
>Could we see the system columns on these rows?
>
> select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where ...
>
> regards, tom lane
>
>
>
>
yes,
01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str;
oid | xmin | cmin | xmax | cmax | ctid | constname |
fid | constvalue
--------+---------+---------+---------+---------+---------+-----------+-----+------------
17910 | 22331 | 2 | 2 | 26679 | (0,7) | ANMGRID
| 0 | SOF_10
17908 | 985 | 6 | 6 | 0 | (0,18) | AKLTYPID
| 0 | SOF_3
17907 | 985 | 30 | 30 | 0 | (0,21) | ADARID
| 0 | SOF_2
17921 | 985 | 34 | 34 | 0 | (0,22) | AOBLASTID
| 0 | SOF_3
17911 | 4640 | 6 | 6 | 26679 | (0,24) | AACCGRID
| 0 | SOF_3
17920 | 1220598 | 2 | 2 | 1475630 | (0,47) | ASLUID
| 0 | SOF_17
17917 | 643083 | 2 | 2 | 1475630 | (0,49) | LOCAID
| 0 | SOF_13
17918 | 762851 | 3 | 3 | 1475630 | (0,50) | AUSERID
| 0 | SOF_3
17923 | 35539 | 165 | 165 | 1475630 | (0,51) | ASETUPID
| 0 | SOF_399
283686 | 514327 | 3 | 3 | 1475630 | (0,52) | NASTRF
| 0 | SOF_1
17909 | 2156667 | 2 | 2 | 2193198 | (0,54) | AKLGRID
| 0 | SOF_8
17922 | 2103298 | 2 | 2 | 2193198 | (0,55) | AGRADID
| 0 | SOF_165
17913 | 2092705 | 2 | 2 | 2193198 | (0,56) | ANOMID
| 0 | SOF_747
63247 | 2226373 | 2 | 2 | 2233003 | (0,126) | TDOCID
| 0 | SOF_47
17914 | 2232893 | 2235861 | 2235861 | 3 | (1,125) | DOCID
| 0 | SOF_25658
17915 | 2232893 | 2235861 | 2235861 | 19 | (3,38) | DOCRID
| 0 | SOF_184547
17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID
| 0 | SOF_19738
17919 | 2232893 | 231 | 231 | 2233003 | (5,104) | DOCPOGPLA
| 0 | SOF_24281
17912 | 2231139 | 2 | 2 | 0 | (6,1) | AKLIID
| 0 | SOF_3513
17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID
| 0 | SOF_19738
63249 | 2226373 | 103 | 103 | 0 | (16,31) | TDOCRID
| 0 | SOF_2439
(21 rows)
and
01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where
constname='DOCPLAID';
oid | xmin | cmin | xmax | cmax | ctid | constname | fid |
constvalue
-------+---------+---------+---------+------+--------+-----------+-----+------------
17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 |
SOF_19738
17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 |
SOF_19738
(2 rows)
regards,
ivan.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-17 17:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 1 reply; 27+ messages in thread
From: Tom Lane @ 2005-02-17 17:26 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
pginfo <pginfo@t1.unisoftbg.com> writes:
> 01=# select * from a_constants_str where constname='DOCPLAID' ;
> constname | fid | constvalue
> -----------+-----+------------
> DOCPLAID | 0 | SOF_19738
> DOCPLAID | 0 | SOF_19738
> (2 rows)
Could we see the system columns on these rows?
select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where ...
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-17 17:34 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 27+ messages in thread
From: pginfo @ 2005-02-17 17:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
Hi,
Tom Lane wrote:
>pginfo <pginfo@t1.unisoftbg.com> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>Could we see the system columns on these rows?
>>>
>>>
>
>
>
>>01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where
>>constname='DOCPLAID';
>> oid | xmin | cmin | xmax | cmax | ctid | constname | fid |
>>constvalue
>>-------+---------+---------+---------+------+--------+-----------+-----+------------
>> 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 |
>>SOF_19738
>> 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 |
>>SOF_19738
>>(2 rows)
>>
>>
>
>Given the identical OID and xmin values, it seems certain that these are
>the "same" row, ie there was only one insertion event. My bet is that
>the one at (7,62) is the original, and that the one at (4,71) is a copy
>that was made by VACUUM FULL trying to move the row to compact the
>table. So the question is how did both copies get to be marked
>simultaneously valid? That should be impossible, unless a disk write
>got dropped. Have you had any system crashes during VACUUM FULL
>operations recently?
>
> regards, tom lane
>
>
>
>
I do not know exact, but it is possible.
This is customer server and I do not have any info about server/os
crash, but I am 100% sure.
I will ask as soon as possible and replay.
Also I will check the logs.
We start pg witl pg_ctl ...-D... -l /logfile and if we can found the
needet info in pg log file I can check it ( I do not nkow what to search
in this file).
We are running vacuum once per 24 h.
The OS is RedHat AS 3.
PostgreSQL is 7.4.1 (but we have the same problem on 7.4.5).
Will upgrade to 8.0 solve this type of problems ?
regards,
ivan.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-17 18:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 27+ messages in thread
From: Tom Lane @ 2005-02-17 18:19 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
pginfo <pginfo@t1.unisoftbg.com> writes:
> Tom Lane wrote:
>> Could we see the system columns on these rows?
> 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where
> constname='DOCPLAID';
> oid | xmin | cmin | xmax | cmax | ctid | constname | fid |
> constvalue
> -------+---------+---------+---------+------+--------+-----------+-----+------------
> 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 |
> SOF_19738
> 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 |
> SOF_19738
> (2 rows)
Given the identical OID and xmin values, it seems certain that these are
the "same" row, ie there was only one insertion event. My bet is that
the one at (7,62) is the original, and that the one at (4,71) is a copy
that was made by VACUUM FULL trying to move the row to compact the
table. So the question is how did both copies get to be marked
simultaneously valid? That should be impossible, unless a disk write
got dropped. Have you had any system crashes during VACUUM FULL
operations recently?
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-17 19:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 2 replies; 27+ messages in thread
From: Tom Lane @ 2005-02-17 19:01 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
pginfo <pginfo@t1.unisoftbg.com> writes:
> Will upgrade to 8.0 solve this type of problems ?
The problem is probably not Postgres' fault at all. I'm wondering about
disks with write cacheing enabled. And you didn't turn off fsync,
I trust?
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-18 04:53 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 27+ messages in thread
From: pginfo @ 2005-02-18 04:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
Hi,
Tom Lane wrote:
>pginfo <pginfo@t1.unisoftbg.com> writes:
>
>
>>Will upgrade to 8.0 solve this type of problems ?
>>
>>
>
>The problem is probably not Postgres' fault at all. I'm wondering about
>disks with write cacheing enabled. And you didn't turn off fsync,
>I trust?
>
>
About fsync (part from postgresql.conf) :
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or
open_datasync
#wal_buffers = 8 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
Also part from pg logfile:
LOG: statistics collector process (PID 2716) exited with exit code 1
LOG: shutting down
LOG: database system is shut down
LOG: could not create IPv6 socket: Address family not supported by protocol
LOG: database system was shut down at 2005-02-11 19:58:26 EET
LOG: checkpoint record is at 2/BAC39188
LOG: redo record is at 2/BAC39188; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 2221145; next OID: 826607
LOG: database system is ready
LOG: recycled transaction log file "00000002000000BA"
LOG: recycled transaction log file "00000002000000BB"
LOG: recycled transaction log file "00000002000000BC"
LOG: recycled transaction log file "00000002000000BD"
LOG: recycled transaction log file "00000002000000BE"
WARNING: index "a_constants_str_pkey" contains 1449 row versions, but
table contains 1422 row versions
HINT: Rebuild the index with REINDEX.
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
LOG: received smart shutdown request
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
LOG: statistics collector process (PID 2713) exited with exit code 1
LOG: shutting down
LOG: database system is shut down
LOG: could not create IPv6 socket: Address family not supported by protocol
LOG: database system was shut down at 2005-02-16 08:32:21 EET
LOG: checkpoint record is at 2/BFAE09EC
LOG: redo record is at 2/BFAE09EC; undo record is at 0/0; shutdown TRUE
Note we was informed about the problem on 2005-02-16 and rebooted the box.
As I see in log file the last restart was on 2005-02-11 and after it all
worked well.
The affected table is critical and the most used table in this system
and if the insert stop to work stop also the system.
I will notice also that in the first case when we found the same problem
an this system worked ~80 users.
Is it possible pg_dump or reindex table or vacuum analyze to make this
problem?
We are using it very regular.
Will we need to stop using vacuum full at all?
regards,
ivan.
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-18 06:18 pginfo <pginfo@t1.unisoftbg.com>
parent: Iain <iain@mst.co.jp>
0 siblings, 0 replies; 27+ messages in thread
From: pginfo @ 2005-02-18 06:18 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
Hi Iain,
Iain wrote:
> Hi Ivan,
>
> Sorry, I can't remember all you said in earlier posts, but I was
> wondering, your log file says:
>
> > HINT: Rebuild the index with REINDEX.
> Did you do that, and did it solve the problem?
>
No it do not solve the problem.
I sendet the log only to show that we do not have any server crash nor
pg restart.
regards,
ivan.
> regards
> Iain
>
> ----- Original Message -----
> From: pginfo <mailto:pginfo@t1.unisoftbg.com>
> To: Tom Lane <mailto:tgl@sss.pgh.pa.us>
> Cc: Richard_D_Levine@raytheon.com
> <mailto:Richard_D_Levine@raytheon.com> ; Michael Glaesemann
> <mailto:grzm@myrealbox.com> ; pgsql-sql@postgresql.org
> <mailto:pgsql-sql@postgresql.org> ; pgsql-sql-owner@postgresql.org
> <mailto:pgsql-sql-owner@postgresql.org>
> Sent: Friday, February 18, 2005 1:53 PM
> Subject: Re: [SQL] pg primary key bug?
>
> Hi,
>
> Tom Lane wrote:
>
>>pginfo <pginfo@t1.unisoftbg.com> writes:
>>
>>
>>>Will upgrade to 8.0 solve this type of problems ?
>>>
>>>
>>
>>The problem is probably not Postgres' fault at all. I'm wondering about
>>disks with write cacheing enabled. And you didn't turn off fsync,
>>I trust?
>>
>>
> About fsync (part from postgresql.conf) :
>
>
> #---------------------------------------------------------------------------
> # WRITE AHEAD LOG
> #---------------------------------------------------------------------------
>
> # - Settings -
>
> #fsync = true # turns forced synchronization on
> or off
> #wal_sync_method = fsync # the default varies across platforms:
> # fsync, fdatasync, open_sync, or
> open_datasync
> #wal_buffers = 8 # min 4, 8KB each
>
> # - Checkpoints -
>
> #checkpoint_segments = 3 # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 300 # range 30-3600, in seconds
> #checkpoint_warning = 30 # 0 is off, in seconds
>
>
> Also part from pg logfile:
>
> LOG: statistics collector process (PID 2716) exited with exit code 1
> LOG: shutting down
> LOG: database system is shut down
> LOG: could not create IPv6 socket: Address family not supported
> by protocol
> LOG: database system was shut down at 2005-02-11 19:58:26 EET
> LOG: checkpoint record is at 2/BAC39188
> LOG: redo record is at 2/BAC39188; undo record is at 0/0;
> shutdown TRUE
> LOG: next transaction ID: 2221145; next OID: 826607
> LOG: database system is ready
> LOG: recycled transaction log file "00000002000000BA"
> LOG: recycled transaction log file "00000002000000BB"
> LOG: recycled transaction log file "00000002000000BC"
> LOG: recycled transaction log file "00000002000000BD"
> LOG: recycled transaction log file "00000002000000BE"
> WARNING: index "a_constants_str_pkey" contains 1449 row versions,
> but table contains 1422 row versions
> HINT: Rebuild the index with REINDEX.
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> ERROR: duplicate key violates unique constraint
> "a_constants_str_pkey"
> LOG: received smart shutdown request
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
> LOG: statistics collector process (PID 2713) exited with exit code 1
> LOG: shutting down
> LOG: database system is shut down
> LOG: could not create IPv6 socket: Address family not supported
> by protocol
> LOG: database system was shut down at 2005-02-16 08:32:21 EET
> LOG: checkpoint record is at 2/BFAE09EC
> LOG: redo record is at 2/BFAE09EC; undo record is at 0/0;
> shutdown TRUE
>
>
> Note we was informed about the problem on 2005-02-16 and rebooted
> the box.
> As I see in log file the last restart was on 2005-02-11 and after
> it all worked well.
> The affected table is critical and the most used table in this
> system and if the insert stop to work stop also the system.
> I will notice also that in the first case when we found the same
> problem an this system worked ~80 users.
>
> Is it possible pg_dump or reindex table or vacuum analyze to make
> this problem?
> We are using it very regular.
>
> Will we need to stop using vacuum full at all?
>
> regards,
> ivan.
>
>
>> regards, tom lane
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-18 06:34 Iain <iain@mst.co.jp>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 27+ messages in thread
From: Iain @ 2005-02-18 06:34 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
Hi Ivan,
Sorry, I can't remember all you said in earlier posts, but I was wondering,
your log file says:
> HINT: Rebuild the index with REINDEX.
Did you do that, and did it solve the problem?
regards
Iain
----- Original Message -----
From: pginfo
To: Tom Lane
Cc: Richard_D_Levine@raytheon.com ; Michael Glaesemann ;
pgsql-sql@postgresql.org ; pgsql-sql-owner@postgresql.org
Sent: Friday, February 18, 2005 1:53 PM
Subject: Re: [SQL] pg primary key bug?
Hi,
Tom Lane wrote:
pginfo <pginfo@t1.unisoftbg.com> writes:
Will upgrade to 8.0 solve this type of problems ?
The problem is probably not Postgres' fault at all. I'm wondering about
disks with write cacheing enabled. And you didn't turn off fsync,
I trust?
About fsync (part from postgresql.conf) :
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or
open_datasync
#wal_buffers = 8 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
Also part from pg logfile:
LOG: statistics collector process (PID 2716) exited with exit code 1
LOG: shutting down
LOG: database system is shut down
LOG: could not create IPv6 socket: Address family not supported by
protocol
LOG: database system was shut down at 2005-02-11 19:58:26 EET
LOG: checkpoint record is at 2/BAC39188
LOG: redo record is at 2/BAC39188; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 2221145; next OID: 826607
LOG: database system is ready
LOG: recycled transaction log file "00000002000000BA"
LOG: recycled transaction log file "00000002000000BB"
LOG: recycled transaction log file "00000002000000BC"
LOG: recycled transaction log file "00000002000000BD"
LOG: recycled transaction log file "00000002000000BE"
WARNING: index "a_constants_str_pkey" contains 1449 row versions, but
table contains 1422 row versions
HINT: Rebuild the index with REINDEX.
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint "a_constants_str_pkey"
LOG: received smart shutdown request
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
LOG: statistics collector process (PID 2713) exited with exit code 1
LOG: shutting down
LOG: database system is shut down
LOG: could not create IPv6 socket: Address family not supported by
protocol
LOG: database system was shut down at 2005-02-16 08:32:21 EET
LOG: checkpoint record is at 2/BFAE09EC
LOG: redo record is at 2/BFAE09EC; undo record is at 0/0; shutdown TRUE
Note we was informed about the problem on 2005-02-16 and rebooted the
box.
As I see in log file the last restart was on 2005-02-11 and after it all
worked well.
The affected table is critical and the most used table in this system and
if the insert stop to work stop also the system.
I will notice also that in the first case when we found the same problem
an this system worked ~80 users.
Is it possible pg_dump or reindex table or vacuum analyze to make this
problem?
We are using it very regular.
Will we need to stop using vacuum full at all?
regards,
ivan.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-21 07:51 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 27+ messages in thread
From: pginfo @ 2005-02-21 07:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql; pgsql-sql-owner@postgresql.org
Hi,
sorry, but we have the case number 3 in with the same problem.
Also this time we do not find any linux box crash nor pg stop or restart.
The pg version is 7.4.2 on dual xeon + scsi running also RedHat 3.0 AS.
In all the cases we are running RedHat AS 3.0.
This system was running for over 12 m. without any problems.
I send also the state of the problem table (also the same) and my
question is:
Need we to stop using vacuum full for now?
And can only vacuum analyze make the same problem in pg?
As I understand the problem is in OS by making vacuum full analyze (as
Tom wrote).
We do not found any problems in OS and the ony solution we see is to
stop using vacuum full analyze.
Also we are using only jdbc to access pg. Is it possible that jdbc to
make this problem?
regards,
ivan.
serv117=# select oid, xmin, cmin, xmax, cmax, ctid, * from a_constants_str ;
oid | xmin | cmin | xmax | cmax | ctid |
constname | fid | constvalue
-----------+---------+---------+---------+---------+----------+-----------+-----+-------------
760807304 | 7357839 | 0 | 0 | 0 | (0,1) |
PARTID | 0 | SOF_79
760807305 | 7357839 | 0 | 0 | 0 | (0,2) |
AACCGRID | 0 | SOF_29
760807306 | 7357839 | 0 | 0 | 0 | (0,3) |
AKLTYPID | 0 | SOF_47
760807307 | 7357839 | 0 | 0 | 0 | (0,4) |
AOBLASTID | 0 | SOF_41
760807308 | 7357839 | 0 | 0 | 0 | (0,5) |
ANMGRID | 0 | SOF_102
760807309 | 7357839 | 0 | 0 | 0 | (0,6) |
LOCAID | 0 | SOF_112
760807310 | 7357839 | 0 | 0 | 0 | (0,7) |
AKLGRID | 0 | SOF_116
760807311 | 7357839 | 0 | 0 | 0 | (0,8) |
ADARID | 0 | SOF_33
760807314 | 7357839 | 0 | 0 | 0 | (0,11) |
ASLUID | 0 | SOF_86
760807315 | 7357839 | 0 | 0 | 0 | (0,12) |
AUSERID | 0 | SOF_28
760807318 | 7357839 | 0 | 0 | 0 | (0,15) |
ANLIZPID | 0 | SOF_100137
760807316 | 7507505 | 3 | 3 | 0 | (0,36) |
ASETUPID | 0 | SOF_4618
760807324 | 7750088 | 7766293 | 7766293 | 2 | (0,92) |
DOCID | 0 | SOF_836141
760807319 | 7740812 | 2 | 2 | 0 | (4,8) |
ANOMID | 0 | SOF_31353
760807325 | 7750088 | 19 | 19 | 0 | (4,111) |
DOCRID | 0 | SOF_2067257
760807326 | 7750088 | 41 | 41 | 7750975 | (6,27) |
DOCPLAID | 0 | SOF_44261
760807327 | 7750088 | 46 | 46 | 7750975 | (7,106) |
DOCPOGPLA | 0 | SOF_58034
760807324 | 7750088 | 7766293 | 7766293 | 1 | (9,107) |
DOCID | 0 | SOF_836141
760807313 | 7680519 | 2 | 2 | 0 | (10,3) |
NASTRF | 0 | SOF_161
760807312 | 7688072 | 2 | 2 | 0 | (10,92) |
AGRADID | 0 | SOF_804
760807324 | 7750088 | 7766293 | 7766293 | 1 | (12,18) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (13,94) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (15,45) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (17,4) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (18,80) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (20,31) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (21,109) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (23,58) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (25,9) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (26,85) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (28,36) |
DOCID | 0 | SOF_836141
760807324 | 7750088 | 7766293 | 7766293 | 1 | (29,114) |
DOCID | 0 | SOF_836141
760807317 | 7702028 | 2 | 2 | 0 | (51,41) |
AMITAID | 0 | SOF_345
760807320 | 7702064 | 2 | 2 | 0 | (51,42) |
ATRANSID | 0 | SOF_458
760807321 | 7707993 | 2 | 2 | 0 | (57,8) |
TDOCID | 0 | SOF_546
760807323 | 7753774 | 3 | 3 | 0 | (59,7) |
AKLIID | 0 | SOF_22695
760807322 | 7707993 | 2385 | 2385 | 0 | (59,95) |
TDOCRID | 0 | SOF_105930
(37 rows)
serv117=# \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)
Tom Lane wrote:
>pginfo <pginfo@t1.unisoftbg.com> writes:
>
>
>>Will upgrade to 8.0 solve this type of problems ?
>>
>>
>
>The problem is probably not Postgres' fault at all. I'm wondering about
>disks with write cacheing enabled. And you didn't turn off fsync,
>I trust?
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-21 22:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 27+ messages in thread
From: Tom Lane @ 2005-02-21 22:51 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
pginfo <pginfo@t1.unisoftbg.com> writes:
> sorry, but we have the case number 3 in with the same problem.
> Also this time we do not find any linux box crash nor pg stop or restart.
Hmm, well there's *something* mighty curious about the history of this
table. The xmin values span a range of almost 400,000 transactions and
yet the oids are twenty-three consecutive values. Is this the only
table you've inserted into during the last 400K transactions?
It's also odd that there's so much empty space (only 37 rows in 60
pages). It's hard to see how that could be unless there were many
updates on the table, but judging from the name and contents of the
table I can hardly see a reason for lots of updates. How is this table
used exactly? Do you do UPDATEs on it? DELETEs? SELECT FOR UPDATE?
Do you do VACUUM, or VACUUM FULL, or both?
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 05:25 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 27+ messages in thread
From: pginfo @ 2005-02-22 05:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
Hi Tom,
Tom Lane wrote:
>pginfo <pginfo@t1.unisoftbg.com> writes:
>
>
>>sorry, but we have the case number 3 in with the same problem.
>>Also this time we do not find any linux box crash nor pg stop or restart.
>>
>>
>
>Hmm, well there's *something* mighty curious about the history of this
>table. The xmin values span a range of almost 400,000 transactions and
>yet the oids are twenty-three consecutive values. Is this the only
>table you've inserted into during the last 400K transactions?
>
>
No.
>It's also odd that there's so much empty space (only 37 rows in 60
>pages). It's hard to see how that could be unless there were many
>updates on the table, but judging from the name and contents of the
>table I can hardly see a reason for lots of updates. How is this table
>used exactly?
>
In this table we store the last value for the ID of part from other tables.
For each table we have one constant in this table. We are using the
table as sequence.
For Example if we nee to insert the next record in some table we make:
select constvalue from a_constants_str where constname ='...' for update;
increase the value and make
update a_constants_str set constvalue= (new value) where...
It is not so easy as I described, but in general this is the case.
Al this is connected with replications and data syncronisation and so on.
> Do you do UPDATEs on it?
>
Yes, see the description.
> DELETEs?
>
No, never.
> SELECT FOR UPDATE?
>
Yes.
>Do you do VACUUM, or VACUUM FULL, or both?
>
Only vacuum full analyze once per day.
Also once per 4h pg_dump (for pg 7.x we do not have any chance to
restore data without full dump. With 8.0 we will test the ne solution
and stop to make pg_dump.
We have > 200 relative big installs for this ERP and all are using pg.
For the last 3 y. we have one time data corruption and in this 3 cases
problem with pkey's.
All the time pg is working relative well .
regards,
ivan.
>
> regards, tom lane
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 08:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 27+ messages in thread
From: Tom Lane @ 2005-02-22 08:42 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
pginfo <pginfo@t1.unisoftbg.com> writes:
> In this table we store the last value for the ID of part from other tables.
> For each table we have one constant in this table. We are using the
> table as sequence.
> For Example if we nee to insert the next record in some table we make:
> select constvalue from a_constants_str where constname ='...' for update;
> increase the value and make
> update a_constants_str set constvalue= (new value) where...
> It is not so easy as I described, but in general this is the case.
> Al this is connected with replications and data syncronisation and so on.
"Connected"? What exactly is hiding under that last comment?
One way I could take your report is that you've found a weird
interaction between SELECT FOR UPDATE and VACUUM FULL that no one else
has seen before. Another way is that you're using some nonstandard
backend extension that has nasty bugs in it.
It is interesting that you say this system has been working well for
years and only recently have you seen problems. To me the obvious
question is "what have you changed recently?" It might not be a bogus
change in itself, but it could have triggered a bug at lower levels.
It's certainly possible that you have your finger on a backend bug,
but if so there's not nearly enough information here for anyone to
find and fix it. You need to be thinking in terms of how to reproduce
the problem so that it can be studied and fixed. "How can I avoid this
problem" is exactly the wrong question to be asking, because even if
avoiding it is all that concerns you, no one can answer with any
confidence until we understand what the failure mechanism is.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 09:33 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 27+ messages in thread
From: pginfo @ 2005-02-22 09:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
Tom Lane wrote:
>pginfo <pginfo@t1.unisoftbg.com> writes:
>
>
>>In this table we store the last value for the ID of part from other tables.
>>For each table we have one constant in this table. We are using the
>>table as sequence.
>>For Example if we nee to insert the next record in some table we make:
>>select constvalue from a_constants_str where constname ='...' for update;
>>increase the value and make
>>update a_constants_str set constvalue= (new value) where...
>>
>>
>
>
>
>>It is not so easy as I described, but in general this is the case.
>>Al this is connected with replications and data syncronisation and so on.
>>
>>
>
>"Connected"? What exactly is hiding under that last comment?
>
>
We are using separate table for sequences and not sequences from pg
direct, because this is built in application method for making
replication and data syncro.
I wish only to clarify the reason of using the table and to describe the
groud for so many updates and select for updates.
Sorry for my bad english ):.
>One way I could take your report is that you've found a weird
>interaction between SELECT FOR UPDATE and VACUUM FULL that no one else
>has seen before. Another way is that you're using some nonstandard
>backend extension that has nasty bugs in it.
>
>It is interesting that you say this system has been working well for
>years and only recently have you seen problems.
>
Yes, exact.
> To me the obvious
>question is "what have you changed recently?"
>
If I know !
In general we do not make any global changes connected to database
access method.
We are using jdbc (jdbc driver from pg) + jboss (java based
application server) + connection pool (biult in jboss).
We are using jdbc with Statement.executeBatch(...) and also direct with
Statement.executeUpdate(...) .
We are using exact the same ide with oracle without any problem ( oracle
have anoder problems and I prefer pg).
> It might not be a bogus
>change in itself, but it could have triggered a bug at lower levels.
>
>It's certainly possible that you have your finger on a backend bug,
>but if so there's not nearly enough information here for anyone to
>find and fix it.
>
I am sure (not 100%) that it is bug. That is the reason to report the
problem.
> You need to be thinking in terms of how to reproduce
>the problem so that it can be studied and fixed.
>
You idea was that we have "vacuum full" + update or select for update in
the same time.
I think it is not the case, because we start vacuum full at 1:00 AM and
no one is working in this time.
Will vacuum full generate this problem if we have locked table in this
time? (It is possible to have locked table in theory)
At this time we do not have info about how to reproduce the problem.
As the first step we will stop using "vacum full" (if needet we will
stop using vacuum analyze too) to try to collect more info.
> "How can I avoid this
>problem" is exactly the wrong question to be asking, because even if
>avoiding it is all that concerns you, no one can answer with any
>confidence until we understand what the failure mechanism is.
>
>
Can we set some log parameters to collect the needet data?
Can you describe more detailed the idea of problem with "vacuum full" +
"update" and can some one make patch if this problem exists in theory
(if I understand you right)?
We can start using this patch and see if the problem will be again .
If you have anoder Idea we are ready to collect the needet data.
> regards, tom lane
>
>
>
>
regards,
ivan.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 11:49 pginfo <pginfo@t1.unisoftbg.com>
parent: Ragnar Hafstað <gnari@simnet.is>
1 sibling, 0 replies; 27+ messages in thread
From: pginfo @ 2005-02-22 11:49 UTC (permalink / raw)
To: Ragnar Hafstað <gnari@simnet.is>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
Ragnar Hafstað wrote:
>On Tue, 2005-02-22 at 10:33 +0100, pginfo wrote:
>
>
>>
>>We are using jdbc (jdbc driver from pg) + jboss (java based
>>application server) + connection pool (biult in jboss).
>>...
>>Will vacuum full generate this problem if we have locked table in this
>>time? (It is possible to have locked table in theory)
>>
>>
>
>I do not know if this is relevant, but I have seen jboss applications
>keep sessions in 'Idle in transaction' state, apparently with some
>locks granted. Would such cases not interfere with vacuum?
>
>gnari
>
>
>
Only to add,
also keeping sme transactions for long time not commited (possible).
regards,
ivan.
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 11:52 Ragnar Hafstað <gnari@simnet.is>
parent: pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 2 replies; 27+ messages in thread
From: Ragnar Hafstað @ 2005-02-22 11:52 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
On Tue, 2005-02-22 at 10:33 +0100, pginfo wrote:
>
>
> We are using jdbc (jdbc driver from pg) + jboss (java based
> application server) + connection pool (biult in jboss).
> ...
> Will vacuum full generate this problem if we have locked table in this
> time? (It is possible to have locked table in theory)
I do not know if this is relevant, but I have seen jboss applications
keep sessions in 'Idle in transaction' state, apparently with some
locks granted. Would such cases not interfere with vacuum?
gnari
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 13:36 Markus Schaber <schabios@logi-track.com>
parent: Ragnar Hafstað <gnari@simnet.is>
1 sibling, 0 replies; 27+ messages in thread
From: Markus Schaber @ 2005-02-22 13:36 UTC (permalink / raw)
To: Ragnar Hafstað <gnari@simnet.is>; +Cc: pginfo <pginfo@t1.unisoftbg.com>; Tom Lane <tgl@sss.pgh.pa.us>; Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
Hi, Ragnar,
Ragnar Hafstað schrieb:
>>We are using jdbc (jdbc driver from pg) + jboss (java based
>>application server) + connection pool (biult in jboss).
>>...
>>Will vacuum full generate this problem if we have locked table in this
>>time? (It is possible to have locked table in theory)
> I do not know if this is relevant, but I have seen jboss applications
> keep sessions in 'Idle in transaction' state, apparently with some
> locks granted. Would such cases not interfere with vacuum?
Most of those "Idle in transaction" problems were caused by suboptimal
handling of BEGIN in the pgjdbc driver, this should be fixed in current
versions of postgres.jar (build 8.0-310).
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
Attachments:
signature.asc (application/pgp-signature, 255B) download
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 16:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 1 reply; 27+ messages in thread
From: Tom Lane @ 2005-02-22 16:37 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
pginfo <pginfo@t1.unisoftbg.com> writes:
> You idea was that we have "vacuum full" + update or select for update in
> the same time.
> I think it is not the case, because we start vacuum full at 1:00 AM and
> no one is working in this time.
Hmm. AFAICT the duplicate row copies could only be produced by vacuum
full, so that's certainly part of the issue. But if vacuum full in
isolation were broken, we'd surely know it; so there must be some other
contributing factor involved that your setup is exercising but other
people are (mostly) not doing.
I agree with the plan to use plain vacuum for awhile and see if that
makes the problem go away. I think it would have to, but maybe I'm
all wet about that.
In the meantime I would suggest seeing if you can distill your
application down into a test case that other people can run to reproduce
the problem. It doesn't matter if the test doesn't make the bug happen
very often, but we have to see the problem happening before we have much
hope of fixing it.
> Will vacuum full generate this problem if we have locked table in this
> time? (It is possible to have locked table in theory)
No, that's hardly likely. vacuum full deals with locks all the time.
> Can you describe more detailed the idea of problem with "vacuum full" +
> "update" and can some one make patch if this problem exists in theory
> (if I understand you right)?
I have no idea what the actual failure mechanism might be.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 16:42 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 27+ messages in thread
From: pginfo @ 2005-02-22 16:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
Tom Lane wrote:
>pginfo <pginfo@t1.unisoftbg.com> writes:
>
>
>>You idea was that we have "vacuum full" + update or select for update in
>>the same time.
>>I think it is not the case, because we start vacuum full at 1:00 AM and
>>no one is working in this time.
>>
>>
>
>Hmm. AFAICT the duplicate row copies could only be produced by vacuum
>full, so that's certainly part of the issue. But if vacuum full in
>isolation were broken, we'd surely know it; so there must be some other
>contributing factor involved that your setup is exercising but other
>people are (mostly) not doing.
>
>I agree with the plan to use plain vacuum for awhile and see if that
>makes the problem go away. I think it would have to, but maybe I'm
>all wet about that.
>
>
Ok, we can still using vacuum full on some installs (with risk to make
problems to customes).
I will to ask if it will be possible to start some querys (I do not know
the query) exactly before running vacuum full and to save the results in
some log file. If it is possible, we will be able to post the results to
the list in case of ne problem and to have some start point for
reproducing the problem. My idea is some one more familiar with pg to
send this querys (if it exists) and we will install it in vacuum scripts.
>In the meantime I would suggest seeing if you can distill your
>application down into a test case that other people can run to reproduce
>the problem. It doesn't matter if the test doesn't make the bug happen
>very often, but we have to see the problem happening before we have much
>hope of fixing it.
>
>
>
>>Will vacuum full generate this problem if we have locked table in this
>>time? (It is possible to have locked table in theory)
>>
>>
>
>No, that's hardly likely. vacuum full deals with locks all the time.
>
>
>
>>Can you describe more detailed the idea of problem with "vacuum full" +
>>"update" and can some one make patch if this problem exists in theory
>>(if I understand you right)?
>>
>>
>
>I have no idea what the actual failure mechanism might be.
>
> regards, tom lane
>
>
regards,
ivan.
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 17:20 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 27+ messages in thread
From: pginfo @ 2005-02-22 17:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
Tom Lane wrote:
>pginfo <pginfo@t1.unisoftbg.com> writes:
>
>
>>I will to ask if it will be possible to start some querys (I do not know
>>the query) exactly before running vacuum full and to save the results in
>>some log file. If it is possible, we will be able to post the results to
>>the list in case of ne problem and to have some start point for
>>reproducing the problem.
>>
>>
>
>Well, you should definitely turn on log_statement across the whole
>installation so that you have a complete record of all SQL commands
>being issued. Make sure the log includes timestamps and PIDs.
>
>I would suggest adding a simple probe for duplicate records to the
>vacuum script. Maybe something like
>
> set enable_indexscan to off;
> select constname,fid,count(*) from a_constants_str
> group by constname,fid having count(*) > 1;
>
>(The indexscan off bit is just paranoia --- I think that an indexscan
>might mask the presence of multiple copies of what's supposedly a unique
>key.) Do this just before and just after the vacuum full command. That
>will at least nail down whether vacuum full is creating the dups, and
>once we see it happen the trace of the day's SQL commands may give some
>ideas where to look.
>
>
Ok, it was my idea.
We will do it and install the script in ~100 servers and will see the
result.
> regards, tom lane
>
>
>
>
regards,
ivan.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg primary key bug?
@ 2005-02-22 18:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 27+ messages in thread
From: Tom Lane @ 2005-02-22 18:04 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
pginfo <pginfo@t1.unisoftbg.com> writes:
> I will to ask if it will be possible to start some querys (I do not know
> the query) exactly before running vacuum full and to save the results in
> some log file. If it is possible, we will be able to post the results to
> the list in case of ne problem and to have some start point for
> reproducing the problem.
Well, you should definitely turn on log_statement across the whole
installation so that you have a complete record of all SQL commands
being issued. Make sure the log includes timestamps and PIDs.
I would suggest adding a simple probe for duplicate records to the
vacuum script. Maybe something like
set enable_indexscan to off;
select constname,fid,count(*) from a_constants_str
group by constname,fid having count(*) > 1;
(The indexscan off bit is just paranoia --- I think that an indexscan
might mask the presence of multiple copies of what's supposedly a unique
key.) Do this just before and just after the vacuum full command. That
will at least nail down whether vacuum full is creating the dups, and
once we see it happen the trace of the day's SQL commands may give some
ideas where to look.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2005-02-22 18:04 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-03 12:53 pg primary key bug? pginfo <pginfo@t1.unisoftbg.com>
2005-02-03 14:14 ` Michael Glaesemann <grzm@myrealbox.com>
2005-02-03 13:52 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-03 16:30 Re: pg primary key bug? Richard_D_Levine@raytheon.com
2005-02-17 15:12 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-17 16:40 ` Michael Fuhr <mike@fuhr.org>
2005-02-17 16:01 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-17 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-17 16:41 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-17 18:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-17 17:34 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-17 19:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-18 04:53 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-18 06:34 ` Iain <iain@mst.co.jp>
2005-02-18 06:18 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-21 07:51 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-21 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-22 05:25 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-22 08:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-22 09:33 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-22 11:52 ` Ragnar Hafstað <gnari@simnet.is>
2005-02-22 11:49 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-22 13:36 ` Markus Schaber <schabios@logi-track.com>
2005-02-22 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-22 16:42 ` pginfo <pginfo@t1.unisoftbg.com>
2005-02-22 18:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-22 17:20 ` pginfo <pginfo@t1.unisoftbg.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox