If you have a check constraint that tests if a boolean column is not
false by just using the column name, pg_dump doesn't include parens
around the check constraint which causes a syntax error when reloading
the database.

Using the following to create a table:
create table test (col1 boolean constraint test check (col1));

pg_dump -c produced the following:
--
-- PostgreSQL database dump
--

SET SESSION AUTHORIZATION 'postgres';

SET SESSION AUTHORIZATION 'bruno';

SET search_path = public, pg_catalog;

DROP TABLE public.test;
SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 3 (OID 2200)
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

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


SET SESSION AUTHORIZATION 'bruno';

--
-- TOC entry 4 (OID 605016)
-- Name: test; Type: TABLE; Schema: public; Owner: bruno
--

CREATE TABLE test (
    col1 boolean,
    CONSTRAINT test CHECK col1
);


--
-- Data for TOC entry 5 (OID 605016)
-- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno
--

COPY test (col1) FROM stdin;
\.


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 2 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';



Sun, 28 Sep 2003 20:14:03 -0500
Bruno Wolff III <bruno@wolff.to>
pg_dump bug in 7.4

I have a fix for this in the patch queue and it will be applied in 24
hours.  If you want to try it, it is at:

	http://momjian.postgresql.org/cgi-bin/pgpatches



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

Bruno Wolff III wrote:
> If you have a check constraint that tests if a boolean column is not
> false by just using the column name, pg_dump doesn't include parens
> around the check constraint which causes a syntax error when reloading
> the database.
> 
> Using the following to create a table:
> create table test (col1 boolean constraint test check (col1));
> 
> pg_dump -c produced the following:
> --
> -- PostgreSQL database dump
> --
> 
> SET SESSION AUTHORIZATION 'postgres';
> 
> SET SESSION AUTHORIZATION 'bruno';
> 
> SET search_path = public, pg_catalog;
> 
> DROP TABLE public.test;
> SET SESSION AUTHORIZATION 'postgres';
> 
> --
> -- TOC entry 3 (OID 2200)
> -- Name: public; Type: ACL; Schema: -; Owner: postgres
> --
> 
> REVOKE ALL ON SCHEMA public FROM PUBLIC;
> GRANT ALL ON SCHEMA public TO PUBLIC;
> 
> 
> SET SESSION AUTHORIZATION 'bruno';
> 
> --
> -- TOC entry 4 (OID 605016)
> -- Name: test; Type: TABLE; Schema: public; Owner: bruno
> --
> 
> CREATE TABLE test (
>     col1 boolean,
>     CONSTRAINT test CHECK col1
> );
> 
> 
> --
> -- Data for TOC entry 5 (OID 605016)
> -- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno
> --
> 
> COPY test (col1) FROM stdin;
> \.
> 
> 
> SET SESSION AUTHORIZATION 'postgres';
> 
> --
> -- TOC entry 2 (OID 2200)
> -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
> --
> 
> COMMENT ON SCHEMA public IS 'Standard public schema';
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

-- 
  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

Sun, 28 Sep 2003 22:04:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump bug in 7.4
If you are referring to my patch, Bruce - that does not fix it.  Mine 
only addresses psql.

I don't think that pg_dump uses pg_get_constraintdef().  It's probably a 
side effect of switching from using consrc to conbin.

Chris


Bruce Momjian wrote:
> I have a fix for this in the patch queue and it will be applied in 24
> hours.  If you want to try it, it is at:
> 
> 	http://momjian.postgresql.org/cgi-bin/pgpatches
> 
> 
> 
> ---------------------------------------------------------------------------
> 
> Bruno Wolff III wrote:
> 
>>If you have a check constraint that tests if a boolean column is not
>>false by just using the column name, pg_dump doesn't include parens
>>around the check constraint which causes a syntax error when reloading
>>the database.
>>
>>Using the following to create a table:
>>create table test (col1 boolean constraint test check (col1));
>>
>>pg_dump -c produced the following:
>>--
>>-- PostgreSQL database dump
>>--
>>
>>SET SESSION AUTHORIZATION 'postgres';
>>
>>SET SESSION AUTHORIZATION 'bruno';
>>
>>SET search_path = public, pg_catalog;
>>
>>DROP TABLE public.test;
>>SET SESSION AUTHORIZATION 'postgres';
>>
>>--
>>-- TOC entry 3 (OID 2200)
>>-- Name: public; Type: ACL; Schema: -; Owner: postgres
>>--
>>
>>REVOKE ALL ON SCHEMA public FROM PUBLIC;
>>GRANT ALL ON SCHEMA public TO PUBLIC;
>>
>>
>>SET SESSION AUTHORIZATION 'bruno';
>>
>>--
>>-- TOC entry 4 (OID 605016)
>>-- Name: test; Type: TABLE; Schema: public; Owner: bruno
>>--
>>
>>CREATE TABLE test (
>>    col1 boolean,
>>    CONSTRAINT test CHECK col1
>>);
>>
>>
>>--
>>-- Data for TOC entry 5 (OID 605016)
>>-- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno
>>--
>>
>>COPY test (col1) FROM stdin;
>>\.
>>
>>
>>SET SESSION AUTHORIZATION 'postgres';
>>
>>--
>>-- TOC entry 2 (OID 2200)
>>-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
>>--
>>
>>COMMENT ON SCHEMA public IS 'Standard public schema';
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend
>>
> 
> 



Mon, 29 Sep 2003 10:53:32 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug in 7.4
Christopher Kings-Lynne wrote:
> If you are referring to my patch, Bruce - that does not fix it.  Mine 
> only addresses psql.
> 
> I don't think that pg_dump uses pg_get_constraintdef().  It's probably a 
> side effect of switching from using consrc to conbin.


Oh, yea.  If forgot the pretty printing only happens in psql.

Can someone generate a reproducable failure?


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


> 
> Chris
> 
> 
> Bruce Momjian wrote:
> > I have a fix for this in the patch queue and it will be applied in 24
> > hours.  If you want to try it, it is at:
> > 
> > 	http://momjian.postgresql.org/cgi-bin/pgpatches
> > 
> > 
> > 
> > ---------------------------------------------------------------------------
> > 
> > Bruno Wolff III wrote:
> > 
> >>If you have a check constraint that tests if a boolean column is not
> >>false by just using the column name, pg_dump doesn't include parens
> >>around the check constraint which causes a syntax error when reloading
> >>the database.
> >>
> >>Using the following to create a table:
> >>create table test (col1 boolean constraint test check (col1));
> >>
> >>pg_dump -c produced the following:
> >>--
> >>-- PostgreSQL database dump
> >>--
> >>
> >>SET SESSION AUTHORIZATION 'postgres';
> >>
> >>SET SESSION AUTHORIZATION 'bruno';
> >>
> >>SET search_path = public, pg_catalog;
> >>
> >>DROP TABLE public.test;
> >>SET SESSION AUTHORIZATION 'postgres';
> >>
> >>--
> >>-- TOC entry 3 (OID 2200)
> >>-- Name: public; Type: ACL; Schema: -; Owner: postgres
> >>--
> >>
> >>REVOKE ALL ON SCHEMA public FROM PUBLIC;
> >>GRANT ALL ON SCHEMA public TO PUBLIC;
> >>
> >>
> >>SET SESSION AUTHORIZATION 'bruno';
> >>
> >>--
> >>-- TOC entry 4 (OID 605016)
> >>-- Name: test; Type: TABLE; Schema: public; Owner: bruno
> >>--
> >>
> >>CREATE TABLE test (
> >>    col1 boolean,
> >>    CONSTRAINT test CHECK col1
> >>);
> >>
> >>
> >>--
> >>-- Data for TOC entry 5 (OID 605016)
> >>-- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno
> >>--
> >>
> >>COPY test (col1) FROM stdin;
> >>\.
> >>
> >>
> >>SET SESSION AUTHORIZATION 'postgres';
> >>
> >>--
> >>-- TOC entry 2 (OID 2200)
> >>-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
> >>--
> >>
> >>COMMENT ON SCHEMA public IS 'Standard public schema';
> >>
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 8: explain analyze is your friend
> >>
> > 
> > 
> 
> 

-- 
  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

Sun, 28 Sep 2003 23:16:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump bug in 7.4
On Sun, Sep 28, 2003 at 23:16:48 -0400,
  Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> Christopher Kings-Lynne wrote:
> > If you are referring to my patch, Bruce - that does not fix it.  Mine 
> > only addresses psql.
> > 
> > I don't think that pg_dump uses pg_get_constraintdef().  It's probably a 
> > side effect of switching from using consrc to conbin.
> 
> 
> Oh, yea.  If forgot the pretty printing only happens in psql.
> 
> Can someone generate a reproducable failure?

You should be able to get my example to work pretty easily.

Create a table in a convenient database using the following:
create table test (col1 boolean constraint test check (col1));

Then pg_dump -c the database. As shown in my example below you
with have a check constraint that is missing parens which will
cause a syntax error if you try to reload the dump.

> 
> 
> ---------------------------------------------------------------------------
> 
> 
> > 
> > Chris
> > 
> > 
> > Bruce Momjian wrote:
> > > I have a fix for this in the patch queue and it will be applied in 24
> > > hours.  If you want to try it, it is at:
> > > 
> > > 	http://momjian.postgresql.org/cgi-bin/pgpatches
> > > 
> > > 
> > > 
> > > ---------------------------------------------------------------------------
> > > 
> > > Bruno Wolff III wrote:
> > > 
> > >>If you have a check constraint that tests if a boolean column is not
> > >>false by just using the column name, pg_dump doesn't include parens
> > >>around the check constraint which causes a syntax error when reloading
> > >>the database.
> > >>
> > >>Using the following to create a table:
> > >>create table test (col1 boolean constraint test check (col1));
> > >>
> > >>pg_dump -c produced the following:
> > >>--
> > >>-- PostgreSQL database dump
> > >>--
> > >>
> > >>SET SESSION AUTHORIZATION 'postgres';
> > >>
> > >>SET SESSION AUTHORIZATION 'bruno';
> > >>
> > >>SET search_path = public, pg_catalog;
> > >>
> > >>DROP TABLE public.test;
> > >>SET SESSION AUTHORIZATION 'postgres';
> > >>
> > >>--
> > >>-- TOC entry 3 (OID 2200)
> > >>-- Name: public; Type: ACL; Schema: -; Owner: postgres
> > >>--
> > >>
> > >>REVOKE ALL ON SCHEMA public FROM PUBLIC;
> > >>GRANT ALL ON SCHEMA public TO PUBLIC;
> > >>
> > >>
> > >>SET SESSION AUTHORIZATION 'bruno';
> > >>
> > >>--
> > >>-- TOC entry 4 (OID 605016)
> > >>-- Name: test; Type: TABLE; Schema: public; Owner: bruno
> > >>--
> > >>
> > >>CREATE TABLE test (
> > >>    col1 boolean,
> > >>    CONSTRAINT test CHECK col1
> > >>);
> > >>
> > >>
> > >>--
> > >>-- Data for TOC entry 5 (OID 605016)
> > >>-- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno
> > >>--
> > >>
> > >>COPY test (col1) FROM stdin;
> > >>\.
> > >>
> > >>
> > >>SET SESSION AUTHORIZATION 'postgres';
> > >>
> > >>--
> > >>-- TOC entry 2 (OID 2200)
> > >>-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
> > >>--
> > >>
> > >>COMMENT ON SCHEMA public IS 'Standard public schema';
> > >>
> > >>
> > >>
> > >>---------------------------(end of broadcast)---------------------------
> > >>TIP 8: explain analyze is your friend
> > >>
> > > 
> > > 
> > 
> > 
> 
> -- 
>   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

Mon, 29 Sep 2003 08:20:07 -0500
Bruno Wolff III <bruno@wolff.to>
Re: pg_dump bug in 7.4
Bruno Wolff III wrote:
> On Sun, Sep 28, 2003 at 23:16:48 -0400,
>   Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> > Christopher Kings-Lynne wrote:
> > > If you are referring to my patch, Bruce - that does not fix it.  Mine 
> > > only addresses psql.
> > > 
> > > I don't think that pg_dump uses pg_get_constraintdef().  It's probably a 
> > > side effect of switching from using consrc to conbin.
> > 
> > 
> > Oh, yea.  If forgot the pretty printing only happens in psql.
> > 
> > Can someone generate a reproducable failure?
> 
> You should be able to get my example to work pretty easily.
> 
> Create a table in a convenient database using the following:
> create table test (col1 boolean constraint test check (col1));
> 
> Then pg_dump -c the database. As shown in my example below you
> with have a check constraint that is missing parens which will
> cause a syntax error if you try to reload the dump.

OK, it's a must-fix bug for 7.4.  Thanks.

-- 
  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

Mon, 29 Sep 2003 10:02:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump bug in 7.4
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> If you are referring to my patch, Bruce - that does not fix it.  Mine 
> only addresses psql.

It strikes me that maybe your patch should add parens always, rather
than just in the prettyprint case.

> I don't think that pg_dump uses pg_get_constraintdef().

As of 7.4 it does, looks like:

    if (g_fout->remoteVersion >= 70400)
        appendPQExpBuffer(chkquery, "SELECT conname, "
                        "pg_catalog.pg_get_constraintdef(oid) AS consrc "
                          "FROM pg_catalog.pg_constraint "
                          "WHERE contypid = '%s'::pg_catalog.oid",
                          tinfo->oid);
    else
        appendPQExpBuffer(chkquery, "SELECT conname, 'CHECK (' || consrc || ')' AS consrc "
                          "FROM pg_catalog.pg_constraint "
                          "WHERE contypid = '%s'::pg_catalog.oid",
                          tinfo->oid);

			regards, tom lane

Mon, 29 Sep 2003 11:04:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump bug in 7.4 
The following is still a problem in current cvs (as of 2 hours ago).
Normally I wouldn't bug people about this again this soon, but with talk of
a release candidate next week I wanted to make sure that it wasn't forgotten.

On Sun, Sep 28, 2003 at 20:14:03 -0500,
  Bruno Wolff III <bruno@wolff.to> wrote:
> If you have a check constraint that tests if a boolean column is not
> false by just using the column name, pg_dump doesn't include parens
> around the check constraint which causes a syntax error when reloading
> the database.
> 
> Using the following to create a table:
> create table test (col1 boolean constraint test check (col1));
> 
> pg_dump -c produced the following:
> --
> -- PostgreSQL database dump
> --
> 
> SET SESSION AUTHORIZATION 'postgres';
> 
> SET SESSION AUTHORIZATION 'bruno';
> 
> SET search_path = public, pg_catalog;
> 
> DROP TABLE public.test;
> SET SESSION AUTHORIZATION 'postgres';
> 
> --
> -- TOC entry 3 (OID 2200)
> -- Name: public; Type: ACL; Schema: -; Owner: postgres
> --
> 
> REVOKE ALL ON SCHEMA public FROM PUBLIC;
> GRANT ALL ON SCHEMA public TO PUBLIC;
> 
> 
> SET SESSION AUTHORIZATION 'bruno';
> 
> --
> -- TOC entry 4 (OID 605016)
> -- Name: test; Type: TABLE; Schema: public; Owner: bruno
> --
> 
> CREATE TABLE test (
>     col1 boolean,
>     CONSTRAINT test CHECK col1
> );
> 
> 
> --
> -- Data for TOC entry 5 (OID 605016)
> -- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno
> --
> 
> COPY test (col1) FROM stdin;
> \.
> 
> 
> SET SESSION AUTHORIZATION 'postgres';
> 
> --
> -- TOC entry 2 (OID 2200)
> -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
> --
> 
> COMMENT ON SCHEMA public IS 'Standard public schema';
> 
> 

Sat, 4 Oct 2003 12:50:27 -0500
Bruno Wolff III <bruno@wolff.to>
Re: pg_dump bug in 7.4
When grilled further on (Sat, 4 Oct 2003 12:50:27 -0500),
Bruno Wolff III <bruno@wolff.to> confessed:

> The following is still a problem in current cvs (as of 2 hours ago).
> Normally I wouldn't bug people about this again this soon, but with talk of
> a release candidate next week I wanted to make sure that it wasn't forgotten.
> 

I just hit the same problem (with 7.4b4).

CREATE TABLE processing (
    "index" integer NOT NULL,
    "time" timestamp with time zone DEFAULT now() NOT NULL,
    archname_index integer,
    CONSTRAINT archname_index CHECK NULL::boolean
);


Cheers,
Rob

-- 
 11:49:30 up 64 days,  4:19,  4 users,  load average: 4.37, 3.83, 3.53
Sat, 4 Oct 2003 11:51:28 -0600
Robert Creager <Robert_Creager@LogicalChaos.org>
Re: pg_dump bug in 7.4

I have added this to the 7.4 open items list:

	Fix pg_dump of CREATE TABLE test (col1 BOOLEAN CONSTRAINT test
	CHECK (col1))


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

Bruno Wolff III wrote:
> The following is still a problem in current cvs (as of 2 hours ago).
> Normally I wouldn't bug people about this again this soon, but with talk of
> a release candidate next week I wanted to make sure that it wasn't forgotten.
> 
> On Sun, Sep 28, 2003 at 20:14:03 -0500,
>   Bruno Wolff III <bruno@wolff.to> wrote:
> > If you have a check constraint that tests if a boolean column is not
> > false by just using the column name, pg_dump doesn't include parens
> > around the check constraint which causes a syntax error when reloading
> > the database.
> > 
> > Using the following to create a table:
> > create table test (col1 boolean constraint test check (col1));
> > 
> > pg_dump -c produced the following:
> > --
> > -- PostgreSQL database dump
> > --
> > 
> > SET SESSION AUTHORIZATION 'postgres';
> > 
> > SET SESSION AUTHORIZATION 'bruno';
> > 
> > SET search_path = public, pg_catalog;
> > 
> > DROP TABLE public.test;
> > SET SESSION AUTHORIZATION 'postgres';
> > 
> > --
> > -- TOC entry 3 (OID 2200)
> > -- Name: public; Type: ACL; Schema: -; Owner: postgres
> > --
> > 
> > REVOKE ALL ON SCHEMA public FROM PUBLIC;
> > GRANT ALL ON SCHEMA public TO PUBLIC;
> > 
> > 
> > SET SESSION AUTHORIZATION 'bruno';
> > 
> > --
> > -- TOC entry 4 (OID 605016)
> > -- Name: test; Type: TABLE; Schema: public; Owner: bruno
> > --
> > 
> > CREATE TABLE test (
> >     col1 boolean,
> >     CONSTRAINT test CHECK col1
> > );
> > 
> > 
> > --
> > -- Data for TOC entry 5 (OID 605016)
> > -- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno
> > --
> > 
> > COPY test (col1) FROM stdin;
> > \.
> > 
> > 
> > SET SESSION AUTHORIZATION 'postgres';
> > 
> > --
> > -- TOC entry 2 (OID 2200)
> > -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
> > --
> > 
> > COMMENT ON SCHEMA public IS 'Standard public schema';
> > 
> > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  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

Sat, 4 Oct 2003 14:15:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump bug in 7.4
Bruno Wolff III <bruno@wolff.to> writes:
> The following is still a problem in current cvs (as of 2 hours ago).

Not any more ;-)

			regards, tom lane

Sat, 04 Oct 2003 14:24:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump bug in 7.4 
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have added this to the 7.4 open items list:

> 	Fix pg_dump of CREATE TABLE test (col1 BOOLEAN CONSTRAINT test
> 	CHECK (col1))

Fixed now.

			regards, tom lane

Sat, 04 Oct 2003 14:38:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump bug in 7.4 
On Sat, Oct 04, 2003 at 14:24:40 -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > The following is still a problem in current cvs (as of 2 hours ago).
> 
> Not any more ;-)

Thanks. I tried it out and it is now working for me.

Sun, 5 Oct 2003 03:17:37 -0500
Bruno Wolff III <bruno@wolff.to>
Re: pg_dump bug in 7.4