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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Jeff Ross" <jross (AT) wykids (DOT) org> writes:
Actually, yes it does. SIMILAR TO (specifically similar_escape()) was
broken for patterns involving | ... but now it's fixed. The previous
code failed to enforce that the pattern be a match to the entire data
string, but that is what is required by my reading of the SQL99 spec.
So your pattern really says that the data value has to be a *single*
letter, digit, or dot. See
http://archives.postgresql.org/pgsql...4/msg00139.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org