X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0FCBF3A438B for ; Mon, 1 Nov 2004 02:36:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55212-09 for ; Mon, 1 Nov 2004 02:36:07 +0000 (GMT) Received: from candle.pha.pa.us (candle.pha.pa.us [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id 610383A402B for ; Mon, 1 Nov 2004 02:36:09 +0000 (GMT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id iA12a6V19009; Sun, 31 Oct 2004 21:36:06 -0500 (EST) From: Bruce Momjian Message-Id: <200411010236.iA12a6V19009@candle.pha.pa.us> Subject: Re: pg_restore TODO - delay PK creation In-Reply-To: <017501c4bfb9$a70b7f20$7201a8c0@mst1x5r347kymb> To: Iain Date: Sun, 31 Oct 2004 21:36:06 -0500 (EST) Cc: pgsql-admin@postgresql.org X-Mailer: ELM [version 2.4ME+ PL108 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/4 X-Sequence-Number: 15389 Iain wrote: > Hi, > > I'm wondering if this is already on some todo list for pg_restore but I > didn't find any mention of it anywhere, so I thought I should post this and > see what people think.. > > Basically, I'd like to see an option at restore time to not include the > primary key constraint when issuing the create table command. I'd like the > PK to be added after data has been loaded using an ALTER command. > > The principle reason for this is performance. > > There may also be a bug somewhere, or perhaps just a problem with my system, > but I was trying to restore a fairly large table (over 7000000 rows) which > would run for a couple hours before failing. Dropping the PK enabled the > load to complete in 3 or 4 minutes. Adding the PK took another 3 or 4 > minutes which adds up to quite a difference. I don't know what PostgreSQL version you have but we currently do what you suggest and I think have been doing it for a few releases now: --------------------------------------------------------------------------- -- -- Name: test; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE test ( x integer NOT NULL ); ALTER TABLE public.test OWNER TO postgres; -- -- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY test (x) FROM stdin; 1 \. -- -- Name: test_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY test ADD CONSTRAINT test_pkey PRIMARY KEY (x); -- 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