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(at)wykids(dot)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