Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I see the values being stored on constriant creation, but not being used
> > anywhere:
>
> I believe the values that actually get inspected at runtime are the
> tgdeferrable and tginitdeferred fields in pg_trigger. The columns in
> pg_constraint are just copies of these.
>
> It is not real clear to me whether it should be allowed to alter the
> deferrability status of a foreign-key constraint --- is that in the spec?
The big problem is that while pg_dump's dump_trigger() looks at
tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
at tginitdeferred, and therefore doesn't record the requirement as part
of ALTER TABLE ADD CONSTRAINT.
Attached is a dump of the supplied example, showing that the outputs are the
same. Looks like this is a must-fix for 7.3.2.
--
Bruce Momjian | http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
--
-- PostgreSQL database dump
--
\connect - postgres
SET search_path = public, pg_catalog;
--
-- TOC entry 2 (OID 149751)
-- Name: prim; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE prim (
i integer NOT NULL
);
--
-- TOC entry 3 (OID 149755)
-- Name: for1; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE for1 (
j integer
);
--
-- TOC entry 4 (OID 149761)
-- Name: for2; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE for2 (
j integer
);
--
-- Data for TOC entry 6 (OID 149751)
-- Name: prim; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY prim (i) FROM stdin;
\.
--
-- Data for TOC entry 7 (OID 149755)
-- Name: for1; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY for1 (j) FROM stdin;
\.
--
-- Data for TOC entry 8 (OID 149761)
-- Name: for2; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY for2 (j) FROM stdin;
\.
--
-- TOC entry 5 (OID 149753)
-- Name: prim_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY prim
ADD CONSTRAINT prim_pkey PRIMARY KEY (i);
--
-- TOC entry 9 (OID 149757)
-- Name: $1; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY for1
ADD CONSTRAINT "$1" FOREIGN KEY (j) REFERENCES prim(i) ON UPDATE NO ACTION ON
DELETE NO ACTION;
--
-- TOC entry 10 (OID 149763)
-- Name: $1; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY for2
ADD CONSTRAINT "$1" FOREIGN KEY (j) REFERENCES prim(i) ON UPDATE NO ACTION ON
DELETE NO ACTION;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
