Re: [HACKERS] pg_dump bug in 7.4
On Sat, Oct 04, 2003 at 14:24:40 -0400, Tom Lane [EMAIL PROTECTED] wrote: Bruno Wolff III [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 [EMAIL PROTECTED] 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
Re: [HACKERS] pg_dump bug in 7.4
When grilled further on (Sat, 4 Oct 2003 12:50:27 -0500), Bruno Wolff III [EMAIL PROTECTED] 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 pgp0.pgp Description: PGP signature
Re: [HACKERS] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump bug in 7.4
Bruno Wolff III [EMAIL PROTECTED] writes: The following is still a problem in current cvs (as of 2 hours ago). Not any more ;-) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump bug in 7.4
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump bug in 7.4
On Sun, Sep 28, 2003 at 23:16:48 -0400, Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump bug in 7.4
Bruno Wolff III wrote: On Sun, Sep 28, 2003 at 23:16:48 -0400, Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_dump bug in 7.4
Christopher Kings-Lynne [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[HACKERS] 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'; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
