Bug #449: there is a problem with foreign key

yves piel (yvespiel(at)hotmail(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
there is a problem with foreign key

Long Description
I have got a 7.1 PGSQL/PG_dump version

I have a table wich references some values of another one with a foreign key. However, somtimes, i have a foreign key error whereas i know that the values exist into the other table since make a "SELECT * FROM table WHERE id='id'" (it's an example. The id is called by rhe foreign key)
I don't understand and I'm becoming crazy ... :)
There is a script whih create a part of my database and some insert at the end to show the problem.
Please in all the case mail me....I don't sleep and i don't eat anymore......(nearly)
In the example, there are two request which return a fk error
and two which make a good insert. However, the data hve been insert
normaly (insert into... value ...) and there only one special caracter (') and with it it works.
the script creation have been generated by pg_dump

I test it by the command : \i myTestFile
I didn't create the pga_* table since they already exist (when i re-create my database i don't change of pga_* tables)

The application which I 'm working on, is a PHP/PGSQL intranet inerface.

the table RDipEta is the only table which return fk error (I think I didn't hard test the others)

I don't know what can I say moreover.

So, if you want to have a looke on my script.....
thank you
yves piel

yvespiel(at)hotmail(dot)com
france

Sample Code
--
-- TOC Entry ID 17 (OID 18947)
--
-- Name: etablissement Type: TABLE Owner: admsuio
--

CREATE TABLE "etablissement" (
"e_libelle" character varying(80) NOT NULL,
"e_localite" character varying(20) NOT NULL,
"e_structurerattachement" character varying(10),
"e_public" boolean,
Constraint "pketablissement" Primary Key ("e_libelle", "e_localite")
);
--
-- TOC Entry ID 19 (OID 19014)
--
-- Name: diplome Type: TABLE Owner: admsuio
--

CREATE TABLE "diplome" (
"di_nivcod" smallint NOT NULL,
"di_groupcod" smallint NOT NULL,
"di_fornum" smallint NOT NULL,
"di_lettrecod" character(1) NOT NULL,
"di_typeforcod" character varying(10) NOT NULL,
"di_libelle" character varying(130),
"di_classeonisep" character varying(10),
Constraint "pkdiplome" Primary Key ("di_nivcod", "di_groupcod", "di_fornum", "di_lettrecod", "di_typeforcod")
);
--
-- TOC Entry ID 22 (OID 19104)
--
-- Name: rdipeta Type: TABLE Owner: admsuio
--

CREATE TABLE "rdipeta" (
"rde_nivcod" smallint NOT NULL,
"rde_groupcod" smallint NOT NULL,
"rde_fornum" smallint NOT NULL,
"rde_lettrecod" character(1) NOT NULL,
"rde_typeforcod" character varying(10) NOT NULL,
"rde_libelle" character varying(15) NOT NULL,
"rde_localite" character varying(20) NOT NULL,
"rde_finitiale" boolean,
"rde_fcontinue" boolean,
"rde_tplein" boolean,
"rde_correspondance" boolean,
"rde_apprentissage" boolean,
"rde_alternance" boolean,
"rde_qualification" boolean,
"rde_autre" character varying(130),
Constraint "pkrdipeta" Primary Key ("rde_nivcod", "rde_groupcod", "rde_fornum", "rde_lettrecod", "rde_typeforcod", "rde_libelle", "rde_localite")
);
--
-- Data for TOC Entry ID 38 (OID 18947) TABLE DATA etablissement
--

COPY "etablissement" FROM stdin;
\.
--
-- Data for TOC Entry ID 40 (OID 19014) TABLE DATA diplome
--

COPY "diplome" FROM stdin;
\.

COPY "rdipeta" FROM stdin;
\.
--
-- TOC Entry ID 27 (OID 19014)
--
-- Name: "uqdi_libelle" Type: INDEX Owner: admsuio
--

CREATE UNIQUE INDEX "uqdi_libelle" on "diplome" using btree ( "di_libelle" "varchar_ops" );
--
-- TOC Entry ID 67 (OID 19095)
--
-- Name: "RI_ConstraintTrigger_19094" Type: TRIGGER Owner: admsuio
--

CREATE CONSTRAINT TRIGGER "fkrmd_diplomekey" AFTER DELETE ON "diplome" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('fkrmd_diplomekey', 'rmodip', 'diplome', 'UNSPECIFIED', 'rmd_nivcod', 'di_nivcod', 'rmd_groupcod', 'di_groupcod', 'rmd_fornum', 'di_fornum', 'rmd_lettrecod', 'di_lettrecod', 'rmd_typeforcod', 'di_typeforcod');

--
-- TOC Entry ID 68 (OID 19097)
--
-- Name: "RI_ConstraintTrigger_19096" Type: TRIGGER Owner: admsuio
--

CREATE CONSTRAINT TRIGGER "fkrmd_diplomekey" AFTER UPDATE ON "diplome" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('fkrmd_diplomekey', 'rmodip', 'diplome', 'UNSPECIFIED', 'rmd_nivcod', 'di_nivcod', 'rmd_groupcod', 'di_groupcod', 'rmd_fornum', 'di_fornum', 'rmd_lettrecod', 'di_lettrecod', 'rmd_typeforcod', 'di_typeforcod');
--
-- TOC Entry ID 75 (OID 19132)
--
-- Name: "RI_ConstraintTrigger_19131" Type: TRIGGER Owner: admsuio
--

CREATE CONSTRAINT TRIGGER "fkrde_diplomekey" AFTER INSERT OR UPDATE ON "rdipeta" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('fkrde_diplomekey', 'rdipeta', 'diplome', 'UNSPECIFIED', 'rde_nivcod', 'di_nivcod', 'rde_groupcod', 'di_groupcod', 'rde_fornum', 'di_fornum', 'rde_lettrecod', 'di_lettrecod', 'rde_typeforcod', 'di_typeforcod');

--
-- TOC Entry ID 69 (OID 19134)
--
-- Name: "RI_ConstraintTrigger_19133" Type: TRIGGER Owner: admsuio
--

CREATE CONSTRAINT TRIGGER "fkrde_diplomekey" AFTER DELETE ON "diplome" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('fkrde_diplomekey', 'rdipeta', 'diplome', 'UNSPECIFIED', 'rde_nivcod', 'di_nivcod', 'rde_groupcod', 'di_groupcod', 'rde_fornum', 'di_fornum', 'rde_lettrecod', 'di_lettrecod', 'rde_typeforcod', 'di_typeforcod');

--
-- TOC Entry ID 70 (OID 19136)
--
-- Name: "RI_ConstraintTrigger_19135" Type: TRIGGER Owner: admsuio
--

CREATE CONSTRAINT TRIGGER "fkrde_diplomekey" AFTER UPDATE ON "diplome" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('fkrde_diplomekey', 'rdipeta', 'diplome', 'UNSPECIFIED', 'rde_nivcod', 'di_nivcod', 'rde_groupcod', 'di_groupcod', 'rde_fornum', 'di_fornum', 'rde_lettrecod', 'di_lettrecod', 'rde_typeforcod', 'di_typeforcod');

--
-- TOC Entry ID 76 (OID 19138)
--
-- Name: "RI_ConstraintTrigger_19137" Type: TRIGGER Owner: admsuio
--

CREATE CONSTRAINT TRIGGER "fkrde_etablissementkey" AFTER INSERT OR UPDATE ON "rdipeta" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('fkrde_etablissementkey', 'rdipeta', 'etablissement', 'UNSPECIFIED', 'rde_libelle', 'e_libelle', 'rde_localite', 'e_localite');

--
-- TOC Entry ID 59 (OID 19140)
--
-- Name: "RI_ConstraintTrigger_19139" Type: TRIGGER Owner: admsuio
--

CREATE CONSTRAINT TRIGGER "fkrde_etablissementkey" AFTER DELETE ON "etablissement" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('fkrde_etablissementkey', 'rdipeta', 'etablissement', 'UNSPECIFIED', 'rde_libelle', 'e_libelle', 'rde_localite', 'e_localite');

--
-- TOC Entry ID 60 (OID 19142)
--
-- Name: "RI_ConstraintTrigger_19141" Type: TRIGGER Owner: admsuio
--

CREATE CONSTRAINT TRIGGER "fkrde_etablissementkey" AFTER UPDATE ON "etablissement" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('fkrde_etablissementkey', 'rdipeta', 'etablissement', 'UNSPECIFIED', 'rde_libelle', 'e_libelle', 'rde_localite', 'e_localite');

---------------------------------

insert into diplome values(15,14,13,'A','DUT','DUT informatique','le DU inf');
insert into etablissement values('mon tablissement','vannes','ubs','t');
insert into etablissement values('l\'tablissement','vannes','ubs','t');
insert into etablissement values('mon etablissement','vannes','ubs','t');
insert into etablissement values('lyce St Paul','vannes','ubs','t');

-- BAD INSERT
insert into rdipeta values(15,14,13,'A','DUT','mon tablissement','vannes',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,'ancune');
-- GOOD INSERT
insert into rdipeta values(15,14,13,'A','DUT','l\'tablissement','vannes',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,'ancune');
-- BAD INSERT
insert into rdipeta values(15,14,13,'A','DUT','mon etablissement','vannes',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,'ancune');
-- GOOD INSERT
insert into rdipeta values(15,14,13,'A','DUT','lyce St Paul','vannes',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,'ancune');

No file was uploaded with this report