David Brain <dbrain@bandwidth.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
