orphaned RI constraints

Somazx Interesting <somazx@home.com> writes:

I'm working with two different postgres installations - they're both
v7.1.2. On one I can drop a table and the related constraint info seems to
go away with the table, on the other the restraint trigger seems to remain
in the system tables and then when I try to delete rows from tables once
related to the dropped table I get errors saying the dropped tabled doesn't
exist - which I'm interpreting as the RI trigger trying to do its thing and
failing.

IIRC, pg_dump scripts made by 7.1 pg_dump did not dump the FROM part of
the trigger definition, so dropping the referenced table of an RI trigger
reloaded from such a dump didn't make the trigger go away.

This is fixed in 7.1.2 (not sure about 7.1.1).

regards, tom lane

On Fri, 6 Jul 2001, Somazx Interesting wrote:

I'm working with two different postgres installations - they're both
v7.1.2. On one I can drop a table and the related constraint info seems to
go away with the table, on the other the restraint trigger seems to remain
in the system tables and then when I try to delete rows from tables once
related to the dropped table I get errors saying the dropped tabled doesn't
exist - which I'm interpreting as the RI trigger trying to do its thing and
failing.

Questions:

1) Is this possible, or should I look for another explanation.

2) Can I fix things by dropping the constraint info from the system tables,
if so how? Is there a function which cleans the system tables checking for
problems like orphaned triggers, functions and sequences?

Is it possible that the one that's failing was restored from an old dump
output? I believe there was a problem (I think resolved) where the
triggers lost track of the other table involved after a dump/restore which
could have this effect.

To fix it, you should be able to use DROP TRIGGER on the appropriate
triggers that were created (you can find these through a select on
pg_trigger, using the tgargs to find the appropriate ones). As a warning,
you need to double quote the trigger name, so for example if you saw the
following rows for the constraint:

782359 | RI_ConstraintTrigger_782384 | 1654 | 9 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000
782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000

you should be able to do
DROP TRIGGER "RI_ConstraintTrigger_782384";
DROP TRIGGER "RI_ConstraintTrigger_782386";

At 01:30 PM 7/6/2001 -0700, you wrote:

On Fri, 6 Jul 2001, Somazx Interesting wrote:

I'm working with two different postgres installations - they're both
v7.1.2. On one I can drop a table and the related constraint info seems to
go away with the table, on the other the restraint trigger seems to remain
in the system tables and then when I try to delete rows from tables once
related to the dropped table I get errors saying the dropped tabled

doesn't

exist - which I'm interpreting as the RI trigger trying to do its thing

and

failing.

Is it possible that the one that's failing was restored from an old dump
output? I believe there was a problem (I think resolved) where the
triggers lost track of the other table involved after a dump/restore which
could have this effect.

I think that is exactly what happened. Tom mentioned 7.1 had that problem
and until yesterday the development server was still v7.1

To fix it, you should be able to use DROP TRIGGER on the appropriate
triggers that were created (you can find these through a select on
pg_trigger, using the tgargs to find the appropriate ones). As a warning,
you need to double quote the trigger name, so for example if you saw the
following rows for the constraint:

782359 | RI_ConstraintTrigger_782384 | 1654 | 9 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000
782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000

you should be able to do
DROP TRIGGER "RI_ConstraintTrigger_782384";
DROP TRIGGER "RI_ConstraintTrigger_782386";

Perfect. I'll do this, thanks for the help.

Andy.

At 01:30 PM 7/6/2001 -0700, you wrote:

To fix it, you should be able to use DROP TRIGGER on the appropriate
triggers that were created (you can find these through a select on
pg_trigger, using the tgargs to find the appropriate ones). As a warning,
you need to double quote the trigger name, so for example if you saw the
following rows for the constraint:

782359 | RI_ConstraintTrigger_782384 | 1654 | 9 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000
782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000

you should be able to do
DROP TRIGGER "RI_ConstraintTrigger_782384";
DROP TRIGGER "RI_ConstraintTrigger_782386";

Hi,

The above doesn't work for me since DROP TRIGGER requires an ON <table
name> argument, and the table which the trigger is on has long since been
dropped.

Is there something else I can try?

Thanks,
Andy.

On Mon, 9 Jul 2001, Somazx Interesting wrote:

At 01:30 PM 7/6/2001 -0700, you wrote:

To fix it, you should be able to use DROP TRIGGER on the appropriate
triggers that were created (you can find these through a select on
pg_trigger, using the tgargs to find the appropriate ones). As a warning,
you need to double quote the trigger name, so for example if you saw the
following rows for the constraint:

782359 | RI_ConstraintTrigger_782384 | 1654 | 9 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000
782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000

you should be able to do
DROP TRIGGER "RI_ConstraintTrigger_782384";
DROP TRIGGER "RI_ConstraintTrigger_782386";

Hi,

The above doesn't work for me since DROP TRIGGER requires an ON <table
name> argument, and the table which the trigger is on has long since been
dropped.

Right (forgot the on table). The triggers *on* the table that was dropped
should be gone, you should only be left with the triggers on the other
table of the constraint, so use that table's name (not the table you
dropped).

Somazx Interesting wrote:

At 01:30 PM 7/6/2001 -0700, you wrote:

To fix it, you should be able to use DROP TRIGGER on the appropriate
triggers that were created (you can find these through a select on
pg_trigger, using the tgargs to find the appropriate ones). As a warning,
you need to double quote the trigger name, so for example if you saw the
following rows for the constraint:

782359 | RI_ConstraintTrigger_782384 | 1654 | 9 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000
782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000

you should be able to do
DROP TRIGGER "RI_ConstraintTrigger_782384";
DROP TRIGGER "RI_ConstraintTrigger_782386";

Hi,

The above doesn't work for me since DROP TRIGGER requires an ON <table
name> argument, and the table which the trigger is on has long since been
dropped.

Is there something else I can try?

That's hard to believe, because tables that get dropped for
sure take all their triggers with them. What's the result of

SELECT relname FROM pg_class WHERE oid = 782359;

Should be there and be either "qqq" or "qqq2". That's the
table name these triggers are fired for.

What's a little confusing is that in your case the
tgconstrrelid contains 782372 and not NULL. I assume from
that that this is not from the database you're having
problems with, right?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Mon, 9 Jul 2001, Jan Wieck wrote:

Somazx Interesting wrote:

At 01:30 PM 7/6/2001 -0700, you wrote:

To fix it, you should be able to use DROP TRIGGER on the appropriate
triggers that were created (you can find these through a select on
pg_trigger, using the tgargs to find the appropriate ones). As a warning,
you need to double quote the trigger name, so for example if you saw the
following rows for the constraint:

782359 | RI_ConstraintTrigger_782384 | 1654 | 9 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000
782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true |
true | <unnamed> | 782372 | false | false
| 6 | | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000

you should be able to do
DROP TRIGGER "RI_ConstraintTrigger_782384";
DROP TRIGGER "RI_ConstraintTrigger_782386";

Hi,

The above doesn't work for me since DROP TRIGGER requires an ON <table
name> argument, and the table which the trigger is on has long since been
dropped.

Is there something else I can try?

That's hard to believe, because tables that get dropped for
sure take all their triggers with them. What's the result of

SELECT relname FROM pg_class WHERE oid = 782359;

Should be there and be either "qqq" or "qqq2". That's the
table name these triggers are fired for.

What's a little confusing is that in your case the
tgconstrrelid contains 782372 and not NULL. I assume from
that that this is not from the database you're having
problems with, right?

The example rows were from my db with some pulled out to make
it a bit more obvious how to get the trigger names.