Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C226D2E026A for ; Fri, 1 Feb 2008 19:41:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 82338-08-2 for ; Fri, 1 Feb 2008 19:41:10 -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 F229B2E0239 for ; Fri, 1 Feb 2008 19:39:14 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so934475waf.5 for ; Fri, 01 Feb 2008 15:39:13 -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=/12SSSwEQcG2LPzvBcuW05XjJHfATG+AqzM1hAEFI+Q=; b=NYg6mDAahhKoOlXmva/DiMw4zhF2eNqGh2P6jG9PfuVyv3IDjcpKTicWmCN20qS5+Dxb72a+X6Mvnkl1q9s8Xdca7OZjlqOxUPzLe1GhlNMnp/M6+O0hGPEqRH1gtjFkl+p1rOks/70Ykqwd9jmTooomeS93fboq84ME3/4zua0= 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=CdXAO4uZMg/oO6/MdSiKcJ3PHrwiU63lTIvhj/3/q2/BY9AUelTx1ULEh86RXbW1P7TDvFwiNUyEoGEm0KwYdPXRpfDMl3dF3VY3xy9PiKwQrBvVsHSE8PicMwBS7STLfjXhRzjciG3AyTMUcWL0j8bimYD9byIjGKZ9vt8HAKg= Received: by 10.114.78.1 with SMTP id a1mr4669042wab.102.1201909153626; Fri, 01 Feb 2008 15:39:13 -0800 (PST) Received: by 10.114.199.4 with HTTP; Fri, 1 Feb 2008 15:39:13 -0800 (PST) Message-ID: <4544e0330802011539j78ab6d2eofcf58e9442031115@mail.gmail.com> Date: Fri, 1 Feb 2008 17:39:13 -0600 From: "Peter Koczan" To: "Tom Lane" Subject: Re: Legacy foreign keys Cc: "Alvaro Herrera" , pgsql-admin@postgresql.org In-Reply-To: <4544e0330802011030l477b0d8ek57909e75ff367128@mail.gmail.com> 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> <4544e0330712111901k1fa50c5dgb26287aff018e98c@mail.gmail.com> <10859.1197428770@sss.pgh.pa.us> <4544e0330712120940i413e3ad6y867fc2a8744f623b@mail.gmail.com> <4544e0330801311613t5cc6741cx1464947c2215f198@mail.gmail.com> <18279.1201845349@sss.pgh.pa.us> <4544e0330802011030l477b0d8ek57909e75ff367128@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/5 X-Sequence-Number: 28079 > I see what you're saying, but the weird thing is that this happened > even when I did a full dump/restore of that database. I'll try out a > few different things (like restoring the tables, then restoring the > triggers). Hopefully I'll be able to suss out the issue or at least > find a workaround. I have a theory as to what's happening. It can find both tables The new smarts in the 8.3 server seem to require a "complete" foreign key, which, I'm guessing, would require additional triggers defining RESTRICT or CASCADE on UPDATEs and DELETEs. I think the equivalent command would be: "ALTER TABLE wspolicy ADD CONSTRAINT "" FOREIGN KEY (dump_policy) REFERENCES atom_dump_policy_values (os);" I restored the entire schema, and only the schema, to the 8.3 server. Then I tried to create the trigger, and it didn't work. $ /s/postgresql-8.3/bin/pg_dump -h sensei -p 5432 -C --schema-only sushi | /s/postgresql-8.3/bin/psql -h mitchell -p 5434 postgres (lots of commands, no errors). $ /s/postgresql-8.3/bin/psql -h mitchell -p 5434 postgres sushi=# SET search_path TO sushi; SET sushi=# \d 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) sushi=# \d atom_os_policy_values Table "sushi.atom_os_policy_values" Column | Type | Modifiers --------+------+----------- os | text | not null Indexes: "su_atom_os_policy_values_pkey" PRIMARY KEY, btree (os) sushi=# CREATE CONSTRAINT TRIGGER "" AFTER INSERT OR UPDATE ON wspolicy FROM 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'); NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY wspolicy(os) REFERENCES atom_os_policy_values(os) DETAIL: Found referencing table's trigger. CREATE TRIGGER Since there are no definitions for what happens for UPDATES or DELETES, and possibly there's something else, it's considered, an "incomplete trigger group" for the foreign key, as the NOTICE says. The other problem is that other legacy FK triggers on that same table have the same value for the first field, "", which will cause name conflicts. I think I've figured it out enough that I could translate the keys manually if I need to. It's not a big deal to me if handling this case isn't included in the 8.3 server, since these are relatively few and far between, but I figured you should know in case other people run into this problem. One more thing. Is there any way to quickly find all the old-style FKs? I tried looking in pg_trigger but it appears that even new, legitimate foreign keys have triggers driving them. Peter