Patrick Welche <prlw1@newn.cam.ac.uk> writes:
>> Anyway the quickest fix seems to be to manually drop the triggers
>> and reconstruct the FK relationships with ALTER TABLE ADD FOREIGN KEY
>> commands. If that seems too messy to do by hand, you can wait till
>> I've got a pg_dump patch to do it for you.
> Just a note on output. Before I had
> \d trans...
> Indexes: firsttimei_idx btree (firsttimei),
> srcpeername_idx btree (sourcepeername)
> Triggers: RI_ConstraintTrigger_14413070,
> RI_ConstraintTrigger_14413073
> after drop trigger/alter table add foreign key:
> \d trans
> Indexes: firsttimei_idx btree (firsttimei),
> srcpeername_idx btree (sourcepeername)
> Foreign Key constraints: $1 FOREIGN KEY (meter_id) REFERENCES meter(id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO
ACTION,
> $2 FOREIGN KEY (stats_id) REFERENCES stats(id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO
ACTION
> I take it the difference is because before tgconstrrelid was zero, and now
> it isn't?
No, the difference is that now there is a pg_constraint entry for the
foreign-key relationship, and the system understands that the triggers
exist to implement that FK constraint so it doesn't show them separately.
Before they were just random triggers and \d didn't have any special
knowledge about them.
The main advantage of having the pg_constraint entry is that you can
ALTER TABLE DROP CONSTRAINT to get rid of the FK constraint (and the
triggers of course). No more manual mucking with triggers.
regards, tom lane
