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