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

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:

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

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.

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

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

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:

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

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

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

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

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.