Thread: 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
Re: BUG #2455: psql failing to restore a table because of a constaint violation.
From
Tom Lane
Date:
"Jeff Ross" <jross@wykids.org> writes: > 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 failure: > psql:/home/_postgresql/wykids20060524.sql:84507: ERROR: new row for > relation "Clearinghouse" violates check > constraint "refnumber_ck" > Here's the record it barfs on: > RefNumber | V207.030 > "refnumber_ck" CHECK ("RefNumber"::text ~ > similar_escape('[A-Z]|[0-9]|.'::text, NULL::text)) > The value in the record cited doesn't violate the constraint, 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-bugs/2006-04/msg00139.php regards, tom lane