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

Hi,

This could well be a recurrence of this issue:

http://archives.postgresql.org/pgsql-general/2007-01/msg01801.php

for which there doesn't seem to have been a resolution.

I am running:

PostgreSQL 8.1.5 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1-r1)

I seem to have wound up with what I can only assume is a constraint and
index sharing the same name:

e.g.

cdr=# drop index cdrimportsession_pkey;
ERROR:  cannot drop index cdrimportsession_pkey because constraint
cdrimportsession_pkey on table cdrimportsession requires it
HINT:  You may drop constraint cdrimportsession_pkey on table
cdrimportsession instead.


cdr=# alter table cdrimportsession drop constraint cdrimportsession_pkey;
NOTICE:  constraint fk_cdrsummary_cdrimportsession on table cdrsummary
depends on index cdrimportsession_pkey
ERROR:  "cdrimportsession_pkey" is an index


So the schema here is fairly straightforward - I have two tables,
cdrimportsession and cdrsummary which has a FK into cdrimportsession.

I discovered this issue while trying to remove the FK constraint from
the cdrsummary table - it complained about cdrimportsession_pkey being
an index.

I can send the output of pgdump -s on this db if this would be helpful.

While it would be great to figure out _why_ this happened it would be
even better to figure out a way of getting around it (I've already tried
  renaming the cdrimportsession_pkey index - it renames, but I then have
same issue just with different constraint/index names) as the tables
involved are pretty huge and a dump/restore isn't really an option.

Thanks,

David.

--
David Brain - bandwidth.com
dbrain@bandwidth.com
919.297.1078

David Brain <dbrain@bandwidth.com> writes:
> This could well be a recurrence of this issue:
> http://archives.postgresql.org/pgsql-general/2007-01/msg01801.php
> for which there doesn't seem to have been a resolution.

I never got a reproduceable case out of the other reporter ... can you
provide one?  His looked like it had something to do with foreign
key constraints named the same as unique/primary key constraints,
but AFAICS 8.1 won't let you create such, so I dunno how he got into
that state.

            regards, tom lane

Not sure I can provide one either I'm afraid, I don't think I did
anything unusual.  But I can provide you with some background as to how
this DB was created so if you can see any steps that may have been
problematic.

This db was created as part of an upgrade to new hardware, using Slony
to replicate the data to copy data and keep things in sync during the
initial test period.

1. Created basic db schema, minus indexes and constraints.
2. Setup Slony replication to copy/sync data from master db.
3. Once syc complete, re-added indexes and constraints to new DB using
pgadmin (by copying the ddl from the original db - again using pgadmin)
4. Removed FK constraint from the old db - worked ok.
5. Attempted to remove FK constraint from new DB - hit the problem I am
seeing now.

The db is now semi production, but I'll happily provide whatever info I
can short of needing a restart (and even a restart could be scheduled if
necessary).

Let me know if I can provide any more info.

David.

Tom Lane wrote:
> David Brain <dbrain@bandwidth.com> writes:
>> This could well be a recurrence of this issue:
>> http://archives.postgresql.org/pgsql-general/2007-01/msg01801.php
>> for which there doesn't seem to have been a resolution.
>
> I never got a reproduceable case out of the other reporter ... can you
> provide one?  His looked like it had something to do with foreign
> key constraints named the same as unique/primary key constraints,
> but AFAICS 8.1 won't let you create such, so I dunno how he got into
> that state.
>
>             regards, tom lane

David Brain <dbrain@bandwidth.com> writes:
> The db is now semi production, but I'll happily provide whatever info I
> can short of needing a restart (and even a restart could be scheduled if
> necessary).
> Let me know if I can provide any more info.

Can you send me the output of "pg_dump -s" (ie, no data, just schema)?
off list please...

            regards, tom lane

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