Bug #781: Broken Indices in 7.3 Beta - Mailing list pgsql-bugs
From | pgsql-bugs@postgresql.org |
---|---|
Subject | Bug #781: Broken Indices in 7.3 Beta |
Date | |
Msg-id | 20020926193335.EE0F64761DF@postgresql.org Whole thread Raw |
List | pgsql-bugs |
Christoph Nelles (evilazrael@evilazrael.de) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description Broken Indices in 7.3 Beta Long Description Hello everybody! I am currently using 7.3 Beta (my data is not so important ;)) and it breaks frequently one index during UPDATEs. It´s an unique index, and there are only 500 records in the table which are updated every 6 minutes. But at some time, the update does not work anymore as Postgresql says that it "cannot insert a duplicate key in to a unique index". After reindexing the index everything will run smoothly again for a few hours. I will attach the Query, error message and the table definition below. It´s not much information i give you, as i don´t know what is relevant to you. With the Version 7.2.1 this error never occured, so it must be a bug within this beta release. Please tell me what information might be relevant to you or if you already know of this bug. Please email directly to me, as i am currently not subscribed to this particular list. I tried to send this message to the mailing-list, but somehow it never showed up there :( In the mean time i probably found the source of the problem. Probably the UPDATE colidates with an VACUUM ANALYZE of theDB which runs often at the same, as both are crontab jobs (UPDATE every 6 minutes, VACUUM every 60 minutes.) Christoph Nelles Sample Code Log excerpt and query : ERROR: Cannot insert a duplicate key into unique index bnt_planets_pkey LOG: statement: UPDATE bnt_planets SET organics=GREATEST(organics + (LEAST(colo nists, 100000000) * 0.005 * 0.5 * prod_organics / 100.0 * 3.004502250375) - (LEA ST(colonists, 100000000) * 0.005 * 0.05 * 3.004502250375), 0),ore=ore + (LEAST(c olonists, 100000000) * 0.005) * 0.25 * prod_ore / 100.0 * 3.004502250375,goods=g oods + (LEAST(colonists, 100000000) * 0.005) * 0.25 * prod_goods / 100.0 * 3.004 502250375,energy=energy + (LEAST(colonists, 100000000) * 0.005) * 0.5 * prod_ene rgy / 100.0 * 3.004502250375,colonists= LEAST((colonists + (colonists * 0.0005 * 3.004502250375)), 100000000),credits=credits * 1.001500750125 + (LEAST(colonist s, 100000000) * 0.005) * 3 * (100.0 - prod_organics - prod_ore - prod_goods - pr od_energy - prod_fighters - prod_torp) / 100.0 * 3.004502250375 WHERE (organics + (LEAST(colonists, 100000000) * 0.005 * 0.5 * prod_organics / 100.0 * 3.0045022 50375) - (LEAST(colonists, 100000000) * 0.005 * 0.05 * 3.004502250375) >= 0) ERROR: current transaction is aborted, queries ignored until end of transaction block table schema : blacknova=# \d bnt_planets Table "public.bnt_planets" Column | Type | Modifiers ---------------+------------------------+--------------------------------------- ------------------------ planet_id | integer | not null default nextval('"bnt_planets _planet_id_seq"'::text) sector_id | integer | not null default '0' name | character varying(256) | organics | bigint | not null default '0' ore | bigint | not null default '0' goods | bigint | not null default '0' energy | bigint | not null default '0' colonists | bigint | not null default '0' credits | bigint | not null default '0' fighters | bigint | not null default '0' torps | bigint | not null default '0' owner | integer | not null default '0' corp | integer | not null default '0' base | character(1) | not null default 'N' sells | character(1) | not null default 'N' prod_organics | real | not null default '20.0' prod_ore | real | not null default '20.0' prod_goods | real | not null default '20.0' prod_energy | real | not null default '20.0' prod_fighters | real | not null default '10.0' prod_torp | real | not null default '10.0' defeated | character(1) | not null default 'N' Indexes: bnt_planets_pkey primary key btree (planet_id), bnt_planets_corp_idx btree (corp), bnt_planets_owner_idx btree ("owner") Check constraints: "$1" ((base = 'Y'::bpchar) OR (base = 'N'::bpchar)) "$2" ((sells = 'Y'::bpchar) OR (sells = 'N'::bpchar)) "$3" ((defeated = 'Y'::bpchar) OR (defeated = 'N'::bpchar)) No file was uploaded with this report
pgsql-bugs by date: