BUG #5876: Incorrectly reported column value - Mailing list pgsql-bugs

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()