Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1F1892E33D4 for ; Tue, 11 Dec 2007 23:01:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57824-06 for ; Tue, 11 Dec 2007 23:01:20 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.180]) by postgresql.org (Postfix) with ESMTP id 6C5422E33E7 for ; Tue, 11 Dec 2007 23:01:20 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so103019waf.5 for ; Tue, 11 Dec 2007 19:01:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=45d4yzsSwMiEghZeHs9HNxIfFV2suUh6Ft6o93UMXp8=; b=n0zVEO66TXqEi2QcjhgBAIoi2Fz1CHqrur8HBf0AkoPaTSkdaRWu9zfMdjYUv53HfIHgVSHF3KCMZXZQEMtju3/b16ci3kN1+Ruf9Amk4QGAndZBtJ8/sF4+rUMgEAZcqmrdWaGXL4aKcht2wraTzZTQ0ofsBZ0pw9fHbTYAxNI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=vRTVg4KrrHzeWYg2hUEPgKoMx8ewBhMtpUExM4uBzG5B1h0xmw6aVWvUK4gTWrZhMfYqYymbpKCk+fHOOWaCR98xfkaKPpOQOdpr4jPet9+/D/Kuo4SuvGho5aNeS8lhaZsNHZ/77rlxjMJAP9DaDdeUA2u+1XCDYD9HP8WfbOI= Received: by 10.141.107.13 with SMTP id j13mr58020rvm.276.1197428478465; Tue, 11 Dec 2007 19:01:18 -0800 (PST) Received: by 10.140.173.19 with HTTP; Tue, 11 Dec 2007 19:01:18 -0800 (PST) Message-ID: <4544e0330712111901k1fa50c5dgb26287aff018e98c@mail.gmail.com> Date: Tue, 11 Dec 2007 21:01:18 -0600 From: "Peter Koczan" To: "Tom Lane" Subject: Re: Legacy foreign keys Cc: "Alvaro Herrera" , pgsql-admin@postgresql.org In-Reply-To: <20427.1197324492@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4544e0330712101112r6aeccfcckef378a7ea1795e07@mail.gmail.com> <20071210195046.GE27126@alvh.no-ip.org> <20427.1197324492@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/57 X-Sequence-Number: 27574 > No, pg_dump isn't involved --- the new smarts are inside the server, > in CREATE CONSTRAINT TRIGGER. It sounds like Peter has a case that is > confusing that new code. Could we see a complete example? Sure, here's the command: $ /s/postgresql-8.2.5/bin/pg_dump -h sensei -p 5432 -C -Fc sushi | /s/postgresql-8.3-beta/bin/pg_restore -h mitchell -p 5434 -C -d postgres Here are the errors: pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1775; 2620 23125843 TRIGGER RI_ConstraintTrigger_23125843 postgres pg_restore: [archiver (db)] could not execute query: ERROR: trigger "" for relation "atom" already exists Command was: CREATE CONSTRAINT TRIGGER "" AFTER INSERT OR UPDATE ON atom FROM state_values NOT DEFERRABLE INITIALLY ... pg_restore: [archiver (db)] Error from TOC entry 1783; 2620 23125845 TRIGGER RI_ConstraintTrigger_23125845 postgres pg_restore: [archiver (db)] could not execute query: ERROR: trigger "" for relation "results" already exists Command was: CREATE CONSTRAINT TRIGGER "" AFTER INSERT OR UPDATE ON results FROM result_values NOT DEFERRABLE INITIA... pg_restore: [archiver (db)] Error from TOC entry 1784; 2620 23125846 TRIGGER RI_ConstraintTrigger_23125846 postgres pg_restore: [archiver (db)] could not execute query: ERROR: trigger "" for relation "results" already exists Command was: CREATE CONSTRAINT TRIGGER "" AFTER INSERT OR UPDATE ON results FROM on_disk_values NOT DEFERRABLE INITI... pg_restore: [archiver (db)] Error from TOC entry 1780; 2620 23125848 TRIGGER RI_ConstraintTrigger_23125848 postgres pg_restore: [archiver (db)] could not execute query: ERROR: trigger "" for relation "offline" already exists Command was: CREATE CONSTRAINT TRIGGER "" AFTER INSERT OR UPDATE ON offline FROM offline_disposition_values NOT DEFE... pg_restore: [archiver (db)] Error from TOC entry 1786; 2620 23125852 TRIGGER RI_ConstraintTrigger_23125852 postgres pg_restore: [archiver (db)] could not execute query: ERROR: trigger "" for relation "wspolicy" already exists Command was: CREATE CONSTRAINT TRIGGER "" AFTER INSERT OR UPDATE ON wspolicy FROM atom_dump_policy_values NOT DEFERR... pg_restore: [archiver (db)] Error from TOC entry 1787; 2620 23125853 TRIGGER RI_ConstraintTrigger_23125853 postgres pg_restore: [archiver (db)] could not execute query: ERROR: trigger "" for relation "wspolicy" already exists Command was: CREATE CONSTRAINT TRIGGER "" AFTER INSERT OR UPDATE ON wspolicy FROM atom_type_values NOT DEFERRABLE IN... WARNING: errors ignored on restore: 6 Table definition in 8.2 server: sushi=> \d sushi.wspolicy Table "sushi.wspolicy" Column | Type | Modifiers ----------------+--------+----------- os | text | not null dump_policy | text | not null atom_type | text | not null file_system | text | not null est_epoch_size | bigint | Indexes: "su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type, file_system) Triggers: "RI_ConstraintTrigger_23125851" AFTER INSERT OR UPDATE ON sushi.wspolicy FROM sushi.atom_os_policy_values NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'wspolicy', 'atom_os_policy_values', 'UNSPECIFIED', 'os', 'os') "RI_ConstraintTrigger_23125852" AFTER INSERT OR UPDATE ON sushi.wspolicy FROM sushi.atom_dump_policy_values NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'wspolicy', 'atom_dump_policy_values', 'UNSPECIFIED', 'dump_policy', 'dump_policy') "RI_ConstraintTrigger_23125853" AFTER INSERT OR UPDATE ON sushi.wspolicy FROM sushi.atom_type_values NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'wspolicy', 'atom_type_values', 'UNSPECIFIED', 'atom_type', 'atom_type') Table definition in 8.3 beta sever: sushi=> \d sushi.wspolicy Table "sushi.wspolicy" Column | Type | Modifiers ----------------+--------+----------- os | text | not null dump_policy | text | not null atom_type | text | not null file_system | text | not null est_epoch_size | bigint | Indexes: "su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type, file_system) Triggers: "" AFTER INSERT OR UPDATE ON sushi.wspolicy FROM sushi.atom_os_policy_values NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'wspolicy', 'atom_os_policy_values', 'UNSPECIFIED', 'os', 'os') It's still trying to create the foreign key as triggers. And the trigger changed its name to "", which ultimately caused the problem, since all the other triggers wanted to be renamed to "" as well. This happened for both the 8.3 and 8.2 pg_dump. Peter