Re: Constraint and Index with same name? (chicken and egg probelm)

David Brain <dbrain(at)bandwidth(dot)com> writes:
Tom Lane wrote:
>> Hm, I don't see fk_cdrsummary_cdrimportsession in there anywhere?

> That is _very_ odd - I can see it in pgadmin, and also in pg_constraint,
> but it's not showing up in pg_dump or on a '\d' in psql.

Oh really? (looks at code...) Hah, I have a theory. Both pg_dump and
psql's \d command assume that tables with pg_class.reltriggers = 0 must
not have any foreign keys, and so they don't bother looking into
pg_constraint for FKs. You mentioned that this was a Slony slave DB,
and I know that Slony sometimes plays tricks with zeroing reltriggers
temporarily. Or it might not be Slony's fault --- if you did a
data-only dump/restore with --disable-triggers and a pre-8.1 pg_dump,
it would also zero reltriggers; then if it failed before putting back
the correct reltriggers value at the end, you could wind up in this
state.

I'm not yet sure how reltriggers = 0 would result in the observed failure,
but if you fix it do things work any better? You should first check
to see if any tables have bogus counts:

SELECT relname, reltriggers FROM pg_class WHERE
reltriggers != (SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid);

If so you can fix them with

UPDATE pg_class SET reltriggers =
(SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid)
WHERE relname = 'whatever';

regards, tom lane