Re: Bug in pg_get_constraintdef (for deferrable constraints) - Mailing list pgsql-hackers

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> 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
  pgman@candle.pha.pa.us               |  (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;