Обсуждение: 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