Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #5891: Unique index is not unique

BUG #5891: Unique index is not unique

From
"Alexander V. Chernikov"
Date:
The following bug has been logged online:

Bug reference:      5891
Logged by:          Alexander V. Chernikov
Email address:      melifaro@ipfw.ru
PostgreSQL version: 8.4.5
Operating system:   FreeBSD 7.3
Description:        Unique index is not unique
Details:

Just in case if formatting is lost, this data is available at
http://postgresql.pastebin.com/4ZZdXEHB

Problem table:

meganet=# \d billing.bc_payments

                                                      Table
"billing.bc_payments"
          Column           |            Type             |
               Modifiers
---------------------------+-----------------------------+------------------
-----------------------------------------------------------
 bc_payment_id             | integer                     | not null default
nextval('billing.bc_payments_bc_payment_id_seq'::regclass)
 contractor_name           | character varying(255)      | not null

 payment_date              | date                        | not null

 payment_commission_number | character varying(255)      | not null

 payment_sum               | numeric(10,4)               | not null

 contractor_account        | character(18)               |

 contractor_bik            | character(8)                |

 inn                       | character varying(16)       |

 payment_comment           | character varying(1024)     |

 is_assigned               | bit(1)                      | not null default
B'0'::"bit"
 assign_comment            | character varying(255)      |

 assign_maker              | integer                     |

 assign_date               | timestamp without time zone |

 file_name                 | character varying(32)       | not null

 line_number               | integer                     | not null

 parse_date                | timestamp without time zone | not null default
now()
Indexes:

    "bc_payments_pkey" PRIMARY KEY, btree (bc_payment_id)

    "bc_payments_payement_idx" UNIQUE, btree (contractor_name, payment_date,
payment_commission_number, payment_sum, contractor_account, contractor_bik,
inn)
Referenced by:


    TABLE "billing.comein_accounts" CONSTRAINT
"comein_accounts_bc_payment_id_fkey" FOREIGN KEY (bc_payment_id) REFERENCES
billing.bc_payments(bc_payment_id)
    TABLE "billing.payments" CONSTRAINT "payments_bc_payment_id_fkey"
FOREIGN KEY (bc_payment_id) REFERENCES billing.bc_payments(bc_payment_id)

Triggers:


    londiste_replika_logger AFTER INSERT OR DELETE OR UPDATE ON
billing.bc_payments FOR EACH ROW EXECUTE PROCEDURE
pgq.logtriga('londiste.replika', 'kvvvvvvvvvvvvvvv', 'billing.bc_payments')

Problem index: bc_payments_payement_idx

Identical records:
meganet=# SELECT count(*), array_agg(bc_payment_id) FROM billing.bc_payments
GROUP BY contractor_name, payment_date, payment_commission_number,
payment_sum, contractor_account, contractor_bik, inn HAVING count(*) > 1;
 count |  array_agg


-------+-------------


     2 | {6376,6380}


     2 | {6947,6605}


(2 rows)

Data for those records (some fields data changed)
meganet=# select * from billing.bc_payments where bc_payment_id in
(6376,6380,6947,6605) order by payment_date;
 bc_payment_id |    contractor_name     | payment_date |
payment_commission_number | payment_sum | contractor_account |
contractor_bik |    inn     |
payment_comment                                             | is_assigned |
assign_comment | assign_maker |        assign_date         |       file_name
      | line_number |         parse_date

---------------+------------------------+--------------+--------------------
-------+-------------+--------------------+----------------+------------+---
----------------------------------------------------------------------------
--------------------------+-------------+----------------+--------------+---
-------------------------+-----------------------+-------------+------------
----------------

          6376 | ООО "XXX             " | 2010-11-09   | 153
         |   3416.2000 | 7028106000000XXXX8 | 44XXXXX6       | 77086XXXX6 |
Доплата за услуги связи по Договору
№2000ZZZ-2000ZZZ/0309 за ноябрь 2010г., включая |НДС 18
% - 521-12. | 1           | ok             |           33 | 2010-11-09
20:08:31.28546  | sb20101109_200651.txt |         424 | 2010-11-09
20:08:31.280599

          6380 | ООО "XXX             " | 2010-11-09   | 153
         |   3416.2000 | 7028106000000XXXX8 | 44XXXXX6       | 77086XXXX6 |
Доплата за услуги связи по Договору
№2000ZZZ-2000ZZZ/0309 за ноябрь 2010г., включая |НДС 18
% - 521-12. | 1           | ok             |           33 | 2010-11-09
20:38:29.387315 | sb20101109_203358.txt |         424 | 2010-11-09
20:38:29.377831

          6947 | ОАО "YYY    "          | 2010-12-03   | 1026
         |  -2200.0000 | 422810268803163353 | 445YYYY6       | 78120YYYY0 |
Оплата по счету N SCSN-67YYYY8 от 22/11/2010 г. В
т.ч.|НДС 335,59 руб.|                                 | 1
| ok             |           33 | 2010-12-09 17:22:28.891094 |
sb20101209_163201.txt |         108 | 2010-12-09 17:22:28.881133

          6605 | ОАО "YYY    "          | 2010-12-03   | 1026
         |  -2200.0000 | 422810268803163353 | 445YYYY6       | 78120YYYY0 |
Оплата по счету N SCSN-67YYYY8 от 22/11/2010 г. В
т.ч.|НДС 335,59 руб.|                                 | 1
| ok             |           33 | 2010-12-09 16:10:00.176578 |
sb20101209_160846.txt |         108 | 2010-12-09 16:10:00.173055

(4 rows)


meganet=# select version();
                                                 version

----------------------------------------------------------------------------
------------------------------
 PostgreSQL 8.4.5 on amd64-portbld-freebsd7.3, compiled by GCC cc (GCC)
4.2.1 20070719  [FreeBSD], 64-bit



Backup for ~pgsql/data directory with whose records exists so I can do some
additional debugging if needed

Re: BUG #5891: Unique index is not unique

From
Robert Haas
Date:
On Thu, Feb 17, 2011 at 4:51 AM, Alexander V. Chernikov
<melifaro@ipfw.ru> wrote:
> Identical records:
> meganet=3D# SELECT count(*), array_agg(bc_payment_id) FROM billing.bc_pay=
ments
> GROUP BY contractor_name, payment_date, payment_commission_number,
> payment_sum, contractor_account, contractor_bik, inn HAVING count(*) > 1;
> =A0count | =A0array_agg
>
>
> -------+-------------
>
>
> =A0 =A0 2 | {6376,6380}
>
>
> =A0 =A0 2 | {6947,6605}

Has this database crashed at any point?

Can you reply-all with the output of SELECT ctid, xmin, xmax,
bc_payment_id FROM billing.bc_payments WHERE bc_payment_id IN
(6376,6380,6947,6605)?

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company