Thread: pg_dump bug in 7.4
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';
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, Pennsylvania19073
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 >> > >
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, Pennsylvania19073
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
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, Pennsylvania19073
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: > 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'; > >
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_indexinteger, 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 testCHECK (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, Pennsylvania19073
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.