Обсуждение: removing duplicated constraints

I was looking through my database schema and noticed that some foreign key
declarations were duplicated.  For example, I found this statement
occurred twice within my DDL:

alter table ports
    add foreign key (category_id)
       references categories (id) on update cascade on delete cascade;

I fixed up the DDL to remove the duplicates.  Then I turned to the 7.2
PostgreSQL database to examine that situation.  I noticed this
duplication:

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"
('<unnamed>', 'ports', 'categories', 'UNSPECIFIED', 'category_id', 'id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"
('<unnamed>', 'ports', 'categories', 'UNSPECIFIED', 'category_id', 'id');

To my eye, those two contraints are identical.  Some may consider that as
a bug (i.e postgres allowing a FK to be duplicated).  I see it as a
feature, which in this case, has been misused.

From what I see, I can easily delete either one of these two constraints
without affecting the relational integrity of the database.  Do you agree?

This is how I'm going to identify the duplicate constraints.

$ pg_dump -s > fp2migration.ddl
$ grep "CREATE CONSTRAINT TRIGGER" ~/fp2migration.ddl  > constraints.txt
$ cat constraints.txt | sort | uniq > constraints.sorted.txt
$ wc -l constraints.txt
      93 constraints.txt
$ wc -l constraints.sorted.txt
      78 constraints.sorted.txt

It looks like I have 15 duplicated constraints to fix up.

Thanks
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

On 2 Mar 2002 at 10:19, Dan Langille wrote:

> CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
> FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'ports', 'categories',
> 'UNSPECIFIED', 'category_id', 'id');
>
> CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
> FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'ports', 'categories',
> 'UNSPECIFIED', 'category_id', 'id');

How is an unnamed trigger deleted?  During a dump, I can find information
such as this:

--
-- TOC Entry ID 211 (OID 998200)
--
-- Name: "RI_ConstraintTrigger_998199" Type: TRIGGER Owner: dan
--

How is that information useful to this situation?
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

On Sat, 2 Mar 2002, Dan Langille wrote:

> On 2 Mar 2002 at 10:19, Dan Langille wrote:
>
> > CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
> > FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> > PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'ports', 'categories',
> > 'UNSPECIFIED', 'category_id', 'id');
> >
> > CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
> > FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> > PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'ports', 'categories',
> > 'UNSPECIFIED', 'category_id', 'id');
>
> How is an unnamed trigger deleted?  During a dump, I can find information
> such as this:

The "<unnamed>" is the constraint name given.  It also gets a trigger name
(see system table pg_trigger) which you can use with DROP TRIGGER
(although in the case of the fk constraint triggers you need to double
quote the name).

On 2 Mar 2002 at 11:09, Stephan Szabo wrote:

> On Sat, 2 Mar 2002, Dan Langille wrote:
>
> > On 2 Mar 2002 at 10:19, Dan Langille wrote:
> >
> > > CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
> > > FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
> > > EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'ports',
> > > 'categories', 'UNSPECIFIED', 'category_id', 'id');
> > >
> > > CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
> > > FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
> > > EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'ports',
> > > 'categories', 'UNSPECIFIED', 'category_id', 'id');
> >
> > How is an unnamed trigger deleted?  During a dump, I can find information
> > such as this:
>
> The "<unnamed>" is the constraint name given.  It also gets a trigger name
> (see system table pg_trigger) which you can use with DROP TRIGGER (although
> in the case of the fk constraint triggers you need to double quote the
> name).

I found the list of duplicate constraints by eyeballing the files I
created using the methods mentioned in previous post (mainly grep and
sort).  Then I would search in the original dump file for the the CREATE
CONSTRAINT statement.  Using the name found there
(RI_ConstraintTrigger_998199), I did this:

  drop TRIGGER "RI_ConstraintTrigger_998189" on categories;

Making sense?
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

> I found the list of duplicate constraints by eyeballing the files I
> created using the methods mentioned in previous post (mainly grep and
> sort).  Then I would search in the original dump file for the the CREATE
> CONSTRAINT statement.  Using the name found there
> (RI_ConstraintTrigger_998199), I did this:
>
>   drop TRIGGER "RI_ConstraintTrigger_998189" on categories;
>
> Making sense?

Well, I'm not sure that you're guaranteed to get the same trigger names
after the restore as the ones in the dump file comments.  You may have to
look in the system table for the actual current trigger name for the
triggers with those arguments.

On 2 Mar 2002 at 23:42, Stephan Szabo wrote:

>
> > I found the list of duplicate constraints by eyeballing the files I
> > created using the methods mentioned in previous post (mainly grep and
> > sort).  Then I would search in the original dump file for the the CREATE
> > CONSTRAINT statement.  Using the name found there
> > (RI_ConstraintTrigger_998199), I did this:
> >
> >   drop TRIGGER "RI_ConstraintTrigger_998189" on categories;
> >
> > Making sense?
>
> Well, I'm not sure that you're guaranteed to get the same trigger names
> after the restore as the ones in the dump file comments.  You may have to
> look in the system table for the actual current trigger name for the
> triggers with those arguments.

There was no restore.  I did the dump in order to find the duplicates.
Then used that information to find and remove them.

cheers
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

On Sun, 3 Mar 2002, Dan Langille wrote:

> On 2 Mar 2002 at 23:42, Stephan Szabo wrote:
>
> >
> > > I found the list of duplicate constraints by eyeballing the files I
> > > created using the methods mentioned in previous post (mainly grep and
> > > sort).  Then I would search in the original dump file for the the CREATE
> > > CONSTRAINT statement.  Using the name found there
> > > (RI_ConstraintTrigger_998199), I did this:
> > >
> > >   drop TRIGGER "RI_ConstraintTrigger_998189" on categories;
> > >
> > > Making sense?
> >
> > Well, I'm not sure that you're guaranteed to get the same trigger names
> > after the restore as the ones in the dump file comments.  You may have to
> > look in the system table for the actual current trigger name for the
> > triggers with those arguments.
>
> There was no restore.  I did the dump in order to find the duplicates.
> Then used that information to find and remove them.

Then yes, that's all you should need to do.