------=_Part_1813_22811.1099034790627
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

You told me restricted foreign key cannot deferable in PostgreSQL 8.0.0 Beta 4.

2004-10-21 22:33 tgl

* doc/src/sgml/ddl.sgml, doc/src/sgml/ref/create_table.sgml,
src/backend/commands/tablecmds.c, src/backend/commands/trigger.c:
Disallow referential integrity actions from being deferred; only
the NO ACTION check is deferrable. This seems to be a closer
approximation to what the SQL spec says than what we were doing
before, and it prevents some anomalous behaviors that are
possible now that triggers can fire during the execution of PL
functions. Stephan Szabo.

Take this test...
Restricted foreign key allows insertion deferrable,
And rejects deletion deferrable.

Why it is not symmetrical ?

------=_Part_1813_22811.1099034790627
Content-Type: text/plain; name="schema.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment; filename="schema.sql"

--
-- PostgreSQL database dump
--

SET client_encoding =3D 'UNICODE';
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;

--
-- TOC entry 1490 (class 0 OID 0)
-- Name: DUMP TIMESTAMP; Type: DUMP TIMESTAMP; Schema: -; Owner:=20
--

-- Started on 2004-10-29 15:02:15 =D6=D0=B9=FA=B1=EA=D7=BC=CA=B1=BC=E4


--
-- TOC entry 1493 (class 0 OID 0)
-- Dependencies: 5
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: jcl
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path =3D public, pg_catalog;

--
-- TOC entry 17 (class 1255 OID 17347)
-- Dependencies: 5
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: jcl
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;


--
-- TOC entry 247 (class 16402 OID 17348)
-- Dependencies: 17
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:=20
--

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;


--
-- TOC entry 18 (class 1255 OID 17349)
-- Dependencies: 5 247
-- Name: phone_01(); Type: FUNCTION; Schema: public; Owner: jcl
--

CREATE FUNCTION phone_01() RETURNS "trigger"
AS $$
begin
if (select count(*) from phone_hot where s_id=3DNEW.s_id limit 1)<1
then
insert into phone_hot(s_id,id) values(NEW.s_id,NEW.id);
end if;
return NEW;
end;
$$
LANGUAGE plpgsql;


--
-- TOC entry 19 (class 1255 OID 17350)
-- Dependencies: 5 247
-- Name: phone_10(); Type: FUNCTION; Schema: public; Owner: jcl
--

CREATE FUNCTION phone_10() RETURNS "trigger"
AS $$
begin
if (select count(*) from phone where s_id=3DOLD.s_id limit 2)<=3D1
then
delete from phone_hot where s_id=3DOLD.s_id;
end if;
return OLD;
end;
$$
LANGUAGE plpgsql;


SET default_with_oids =3D false;

--
-- TOC entry 1160 (class 1259 OID 17351)
-- Dependencies: 1476 5
-- Name: phone; Type: TABLE; Schema: public; Owner: jcl
--

CREATE TABLE phone (
s_id integer NOT NULL,
id character varying(11) NOT NULL,
CONSTRAINT phone_check_id CHECK (((id)::text ~ '^[0-9]{7,}$'::text))
);


--
-- TOC entry 1161 (class 1259 OID 17354)
-- Dependencies: 5
-- Name: phone_hot; Type: TABLE; Schema: public; Owner: jcl
--

CREATE TABLE phone_hot (
s_id integer NOT NULL,
id character varying(11) NOT NULL
);


--
-- TOC entry 1162 (class 1259 OID 17356)
-- Dependencies: 5
-- Name: student; Type: TABLE; Schema: public; Owner: jcl
--

CREATE TABLE student (
id integer NOT NULL,
name character varying(8) NOT NULL
);


--
-- TOC entry 1482 (class 16386 OID 17359)
-- Dependencies: 1161 1161
-- Name: phone_hot_pkey; Type: CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY phone_hot
ADD CONSTRAINT phone_hot_pkey PRIMARY KEY (s_id);


--
-- TOC entry 1479 (class 16386 OID 17361)
-- Dependencies: 1160 1160
-- Name: phone_pkey; Type: CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY phone
ADD CONSTRAINT phone_pkey PRIMARY KEY (id);


--
-- TOC entry 1484 (class 16386 OID 17363)
-- Dependencies: 1162 1162
-- Name: student_name_key; Type: CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY student
ADD CONSTRAINT student_name_key UNIQUE (name);


--
-- TOC entry 1486 (class 16386 OID 17365)
-- Dependencies: 1162 1162
-- Name: student_pkey; Type: CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY student
ADD CONSTRAINT student_pkey PRIMARY KEY (id);


--
-- TOC entry 1480 (class 1259 OID 17366)
-- Dependencies: 1161
-- Name: phone_hot_id_key; Type: INDEX; Schema: public; Owner: jcl
--

CREATE UNIQUE INDEX phone_hot_id_key ON phone_hot USING btree (id);


--
-- TOC entry 1477 (class 1259 OID 17367)
-- Dependencies: 1160 1160
-- Name: phone_key; Type: INDEX; Schema: public; Owner: jcl
--

CREATE UNIQUE INDEX phone_key ON phone USING btree (s_id, id);


--
-- TOC entry 1488 (class 16386 OID 17370)
-- Dependencies: 1161 1162 1485
-- Name: phone_hot_s_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: j=
cl
--

ALTER TABLE ONLY phone_hot
ADD CONSTRAINT phone_hot_s_id_fkey FOREIGN KEY (s_id) REFERENCES studen=
t(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 1489 (class 16386 OID 17374)
-- Dependencies: 1161 1161 1160 1160 1477
-- Name: phone_hot_s_id_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner=
: jcl
--

ALTER TABLE ONLY phone_hot
ADD CONSTRAINT phone_hot_s_id_id_fkey FOREIGN KEY (s_id, id) REFERENCES=
phone(s_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY=
DEFERRED;


--
-- TOC entry 1487 (class 16386 OID 17378)
-- Dependencies: 1161 1481 1160
-- Name: phone_s_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY phone
ADD CONSTRAINT phone_s_id_fkey FOREIGN KEY (s_id) REFERENCES phone_hot(=
s_id) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;


--
-- TOC entry 1495 (class 0 OID 0)
-- Name: DUMP TIMESTAMP; Type: DUMP TIMESTAMP; Schema: -; Owner:=20
--

-- Completed on 2004-10-29 15:02:15 =D6=D0=B9=FA=B1=EA=D7=BC=CA=B1=BC=E4


--
-- TOC entry 1494 (class 0 OID 0)
-- Dependencies: 5
-- Name: public; Type: ACL; Schema: -; Owner: jcl
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM jcl;
GRANT ALL ON SCHEMA public TO jcl;
GRANT ALL ON SCHEMA public TO PUBLIC;



------=_Part_1813_22811.1099034790627
Content-Type: text/plain; name="test.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment; filename="test.sql"

insert into student values(1,'A');
insert into student values(2,'B');


insert into phone_hot values(1,'0000003');
insert into phone values(1,'0000003');

start transaction;
insert into phone_hot values(1,'0000004');
insert into phone values(1,'0000004');
commit;

insert into phone values(1,'0000005');

update phone_hot set id=3D'0000005' where s_id=3D1;

delete from phone where id=3D'0000004';

delete from phone where id=3D'0000005';
delete from phone_hot where s_id=3D1;

start transaction;
delete from phone where id=3D'0000005';
delete from phone_hot where s_id=3D1;
commit;

------=_Part_1813_22811.1099034790627
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

------=_Part_1813_22811.1099034790627--

On Fri, 29 Oct 2004, [UTF-8] =E6^]=A8=E9^B^U wrote:

There's two separate concepts in foreign keys that are related.

There's the constraint check which makes sure that the constraint is
satisfied (ie, there is an appropriate matching row for each row from
the referencing table). This is deferrable.

There's also referential actions which occur when you change the
referenced table. These are not deferrable.

---

When you insert into a referencing table, at constraint check time (which
may be deferred), the check is applied.

When you delete from a referenced table, if there is a referential action
associated with the foreign key, it is done non-deferred. Technically,
the SQL model would also have a check a constraint check time, however in
your case the error from the referential action happens before that would
occur.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match