The following bug has been logged online:
Bug reference: 5876
Logged by: Dag Lem
Email address: dag@nimrod.no
PostgreSQL version: 9.0.3
Operating system: CentOS release 5.5, i686
Description: Incorrectly reported column value
Details:
The effect of this (pretty serious) bug is that I am able to insert a value
into an integer column and consistently get an entirely different value read
out later.
Unfortunately this happens on a large data set (3.6M rows), and I am not
able to either reproduce with a smaller data set nor provide the data set. I
realize that this is asking you to find a needle in the haystack. However at
least now you're made aware of the bug - please let me know if there's
anything I can do to help isolate it.
The table definition is attached to the end of this report.
I have used batch inserts of 10 000 rows each, commiting after each batch
insert (via DBD::Pg 2.17.1).
The incorrect row is row number 1601050 in the data set, i.e. the 1050th row
in a batch insert.
This is how the bug manifests itself (election_event_pk is always 2 on
insert):
evote=> select distinct election_event_pk from voter where election_event_pk
= 2;
election_event_pk
-------------------
2
(1 row)
evote=> select election_event_pk, count(*) from voter where
election_event_pk = 2 group by election_event_pk;
election_event_pk | count
-------------------+---------
2 | 3611505
(1 row)
evote=> select election_event_pk from voter where election_event_pk = 2 and
voter_id = '<11 digit ssn>';
election_event_pk
-------------------
262146
(1 row)
As you can see, the reported column value is different from what is inserted
(and indexed).
I should mention that I got another problem with the same data set on
PostgreSQL 9.0.2, IIRC "unrecognized rbtree node state: 87" (not 100% sure
about the state number since I immediately upgraded to 9.0.3 and got the
present bug instead).
Again, please let me know if there is anything I can do to help isolate the
bug - table definition below.
Best regards,
Dag Lem
Table "admin.voter"
Column | Type |
Modifiers
---------------------------+--------------------------+---------------------
-------------------------------------
voter_pk | bigint | not null default
nextval('voter_voter_pk_seq'::regclass)
audit_oplock | integer | not null default 0
audit_user | character varying(64) | not null
audit_operator | character varying(64) |
audit_operation | character(1) | not null
audit_timestamp | timestamp with time zone | not null
election_event_pk | integer | not null
voter_id | character varying(11) | not null
date_of_birth | date |
voter_number | integer |
import_batch_number | integer |
country_id | character(2) | not null
county_id | character(2) | not null
municipality_id | character(4) | not null
borough_id | character(6) | not null
polling_district_id | character(4) | not null
mv_area_pk | integer |
eligible | boolean | not null
name_line | character varying(152) | not null
first_name | character varying(50) | not null
middle_name | character varying(50) |
last_name | character varying(50) | not null
address_line1 | character varying(50) |
address_line2 | character varying(50) |
address_line3 | character varying(50) |
postal_code | character varying(4) |
post_town | character varying(50) |
email | character varying(129) |
telephone_number | character varying(35) |
mailing_address_specified | boolean |
mailing_address_line1 | character varying(50) |
mailing_address_line2 | character varying(50) |
mailing_address_line3 | character varying(50) |
mailing_country_code | character varying(50) |
approval_request | character varying(150) |
approved | boolean | not null default
false
date_time_submitted | timestamp with time zone | not null
aarsakskode | character(2) |
endringstype | character(1) |
statuskode | character(1) |
reg_dato | date |
spes_reg_type | character(1) |
electoral_roll_page | integer |
electoral_roll_line | integer |
Indexes:
"voter_pkey" PRIMARY KEY, btree (voter_pk)
"nk_voter" UNIQUE, btree (election_event_pk, voter_id)
"uk_voter_voter_number" UNIQUE, btree (mv_area_pk, voter_number)
"ix_voter_address" btree (election_event_pk, postal_code,
upper(address_line1::text) text_pattern_ops, upper(address_line2::text)
text_pattern_ops, upper(address_line3::text) text_pattern_ops) WITH
(fillfactor=70)
"ix_voter_date_of_birth" btree (election_event_pk, date_of_birth) WITH
(fillfactor=70)
"ix_voter_polling_district" btree (mv_area_pk) WITH (fillfactor=70)
"ix_voter_tsvector" gin (soundex_tsvector(election_event_pk,
name_line::text)) WITH (fastupdate=on)
Foreign-key constraints:
"fk_voter_x_election_event" FOREIGN KEY (election_event_pk) REFERENCES
election_event(election_event_pk) ON DELETE CASCADE
"fk_voter_x_mv_area" FOREIGN KEY (mv_area_pk) REFERENCES
mv_area(mv_area_pk) ON DELETE SET NULL
Referenced by:
TABLE "voting" CONSTRAINT "fk_voting_x_voter" FOREIGN KEY (voter_pk)
REFERENCES voter(voter_pk) ON DELETE CASCADE
Triggers:
voter_insert BEFORE INSERT ON voter FOR EACH ROW EXECUTE PROCEDURE
voter_insert()
voter_update BEFORE UPDATE ON voter FOR EACH ROW EXECUTE PROCEDURE
voter_update()
voter_z_audit BEFORE INSERT OR DELETE OR UPDATE ON voter FOR EACH ROW
EXECUTE PROCEDURE audit_voter()