From: Peter Koczan <pjkoczan@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: Legacy foreign keys
Date: Fri, 1 Feb 2008 17:39:13 -0600
Message-ID: <4544e0330802011539j78ab6d2eofcf58e9442031115@mail.gmail.com> (raw)
In-Reply-To: <4544e0330802011030l477b0d8ek57909e75ff367128@mail.gmail.com>
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>
> 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 "<unnamed>" 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 "<unnamed>"
AFTER INSERT OR UPDATE ON wspolicy
FROM atom_os_policy_values
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'wspolicy',
'atom_os_policy_values', 'UNSPECIFIED', 'os', 'os');
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>"
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, "<unnamed>", 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pjkoczan@gmail.com, tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org
Subject: Re: Legacy foreign keys
In-Reply-To: <4544e0330802011539j78ab6d2eofcf58e9442031115@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox