BUG #2455: psql failing to restore a table because of a constaint violation.

The following bug has been logged online:

Bug reference:      2455
Logged by:          Jeff Ross
Email address:      jross@wykids.org
PostgreSQL version: 8.1.4
Operating system:   OpenBSD 3.9 -current
Description:        psql failing to restore a table because of a constaint
violation.
Details:

After the upgrade to 8.1.4, this script I use to sync a development database
with our live database began failing.

Here's the script:

#!/bin/sh
#backup script for postgresql databases
#
DATE=`date +%Y%m%d`
#dump the live wykids database
/usr/local/bin/pg_dumpall -p 5432 -c > \
        /home/_postgresql/wykids$DATE.sql
#drop the development wykids database
/usr/local/bin/dropdb -p 5435 wykids
#recreate the development wykids database from the dump file we just made
/usr/local/bin/psql -p 5435 template1 -f \
        /home/_postgresql/wykids$DATE.sql

Here's the failure:

psql:/home/_postgresql/wykids20060524.sql:84507: ERROR:  new row for
relation "Clearinghouse" violates check
constraint "refnumber_ck"

CONTEXT:  COPY Clearinghouse, line 1: "Video Three R's for Special Education
School Age Uniqueness and
Cultural Awareness 0.5 total 49.9500..."

Here's the record it barfs on:

wykids=# select * from "Clearinghouse" where "Training Material"
ilike('%three r%');
-[ RECORD 1 ]-----+----------------------------------
Type              | Video
Training Material | Three R's for Special Education
Category          | School Age
Section Found In  | Uniqueness and Cultural Awareness
Clock Hours       | 0.5
Notes             | total
Price             | 49.95
# books           | 1
RefNumber         | V207.030

Here's the table structure:

wykids=# \d "Clearinghouse"
             Table "public.Clearinghouse"
      Column       |         Type          | Modifiers
-------------------+-----------------------+-----------
 Type              | character varying(50) |
 Training Material | character varying(75) |
 Category          | character varying(50) |
 Section Found In  | character varying(50) |
 Clock Hours       | real                  |
 Notes             | character varying(50) |
 Price             | double precision      |
 # books           | character varying(10) |
 RefNumber         | character varying(30) | not null
Indexes:
    "clearinghouse_old_pk" PRIMARY KEY, btree ("RefNumber")
Check constraints:
    "refnumber_ck" CHECK ("RefNumber"::text ~
similar_escape('[A-Z]|[0-9]|.'::text, NULL::text))
Rules:
    refnumber_uppercase_ins AS
    ON INSERT TO "Clearinghouse" DO  UPDATE "Clearinghouse" SET
"RefNumber" = upper(new."RefNumber"::text)
  WHERE "Clearinghouse"."RefNumber"::text = new."RefNumber"::text

The value in the record cited doesn't violate the constraint, and removing
that record from the .sql file
caused the same failure on the very next record.

Using pg_dump -Fc instead also failed.

As a workaround, we dropped the constraint (not critical) to make sure we
still had backup capability.

Jeff Ross