Bug #781: Broken Indices in 7.3 Beta

Christoph Nelles (evilazrael(at)evilazrael(dot)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. Its 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.

Its not much information i give you, as i dont 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 the DB 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