Skip site navigation (1) Skip section navigation (2)

BUG #5876: Incorrectly reported column value

From: "Dag Lem" <dag(at)nimrod(dot)no>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5876: Incorrectly reported column value
Date: 2011-02-10 10:18:23
Message-ID: 201102101018.p1AAINGa001460@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5876
Logged by:          Dag Lem
Email address:      dag(at)nimrod(dot)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()

Responses

pgsql-bugs by date

Next:From: SavitaDate: 2011-02-10 11:02:40
Subject: BUG #5877: problem with wild char used in where clause
Previous:From: Andres FreundDate: 2011-02-10 09:50:38
Subject: Re: crash in 9.1's psql:describeOneTableDetails

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group