The following bug has been logged online: Bug reference: 5891 Logged by: Alexander V. Chernikov Email address: melif...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
