Bug #781: Broken Indices in 7.3 Beta

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