1257 Upgrade Can Fail With Duplicate Row

[Jason Stephenson]

The 1257.schema.materialized_payment_by_billing_type.sql database upgrade script can fail on production data with a duplicate key value error in the data update transaction block:

BEGIN
CREATE FUNCTION
psql:Open-ILS/src/sql/Pg/upgrade/1257.schema.materialized_payment_by_billing_type.sql:592: ERROR: duplicate key value violates unique constraint "x_p_b_once"
DETAIL: Key (xact, payment, billing)=(37654016, 11059981, 52495988) already exists.
CONTEXT: SQL statement "INSERT INTO money.materialized_payment_by_billing_type (
            xact, payment, billing, payment_ts, billing_ts,
            payment_type, billing_type, amount, billing_ou, payment_ou
        ) SELECT xact, payment, billing, payment_ts, billing_ts,
                    payment_type, billing_type, amount, billing_ou, payment_ou
          FROM money.payment_by_billing_type( p.xact )"
PL/pgSQL function tmp_populate_p_b_bt() line 10 at SQL statement
psql:Open-ILS/src/sql/Pg/upgrade/1257.schema.materialized_payment_by_billing_type.sql:594: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

I will investigate the circumstances and an appropriate fix.

[Galen Charlton (gmc)]

Have you had an opportunity to track this down?

[Galen Charlton (gmc)]

As discussed in IRC, I've reverted bug 1174498. This bug remains valid, however, as a blocker for committing 1174498 again.