Thread: When an index and a constraint have the same name...

Seems as though I've gotten myself into something of a pickle:

I wound up with a fkey constraint and an index on the same table having the
same name ('rs_fkey').

The result is an error message when I try to drop the table (cascade) or
even drop the constraint:

# alter table report_specification drop constraint rs_pkey;
NOTICE:  constraint pr_rs on table purchase_record depends on index rs_pkey
ERROR:  "rs_pkey" is an index

and then there is:

# drop index rs_pkey;
ERROR:  cannot drop index rs_pkey because constraint rs_pkey on table
report_specification requires it
HINT:  You may drop constraint rs_pkey on table report_specification instead.

Is there some name-spacing trick I can use to selectively drop the index or
the constraint in a way that postgres (using v8.1) will let me get away with
it?

Thanks,
Jason

jason@buberel.org writes:
> Seems as though I've gotten myself into something of a pickle:
> I wound up with a fkey constraint and an index on the same table having the
> same name ('rs_fkey').

That shouldn't be a problem particularly.

> The result is an error message when I try to drop the table (cascade) or
> even drop the constraint:
> # alter table report_specification drop constraint rs_pkey;
> NOTICE:  constraint pr_rs on table purchase_record depends on index rs_pkey
> ERROR:  "rs_pkey" is an index

That seems odd.  What PG version is this exactly ("8.1" is not good
enough)?  What does psql show for "\d report_specification" and
"\d purchase_record"?

            regards, tom lane

Thanks for taking a look Tom:

I am running postgres 8.1.4 on RedHet (CentOS) v4.0. Here is the
description of the purchase_record table (somewhat abbreviated with
uninvolved columns omitted):

# \d purchase_record
                              Table "public.purchase_record"
           Column           |  Type   |                     Modifiers
----------------------------+---------+----------------------------------------------------
 purchase_record_id         | bigint  | not null default 0
 report_specification_id    | bigint  |
Indexes:
    "pr_pkey" PRIMARY KEY, btree (purchase_record_id)
    "fki_pr_rs" btree (report_specification_id)
Foreign-key constraints:
    "pr_rs" FOREIGN KEY (report_specification_id) REFERENCES
report_specification(report_specification_id) ON UPDATE RESTRICT ON
DELETE CASCADE


# \d report_specification
                           Table "public.report_specification"
         Column          |         Type          |               Modifiers
-------------------------+-----------------------+---------------------------------------
 report_specification_id | bigint                | not null
 report_template_id      | bigint                |
Indexes:
    "rs_pkey" PRIMARY KEY, btree (report_specification_id)
    "fki_rs_rt_fkey" btree (report_template_id)

Regards,
Jason

Tom Lane wrote:
> jason@buberel.org writes:
>
>> Seems as though I've gotten myself into something of a pickle:
>> I wound up with a fkey constraint and an index on the same table having the
>> same name ('rs_fkey').
>>
>
> That shouldn't be a problem particularly.
>
>
>> The result is an error message when I try to drop the table (cascade) or
>> even drop the constraint:
>> # alter table report_specification drop constraint rs_pkey;
>> NOTICE:  constraint pr_rs on table purchase_record depends on index rs_pkey
>> ERROR:  "rs_pkey" is an index
>>
>
> That seems odd.  What PG version is this exactly ("8.1" is not good
> enough)?  What does psql show for "\d report_specification" and
> "\d purchase_record"?
>
>             regards, tom lane
>

"Jason L. Buberel" <jason@buberel.org> writes:
> Thanks for taking a look Tom:
> I am running postgres 8.1.4 on RedHet (CentOS) v4.0. Here is the
> description of the purchase_record table (somewhat abbreviated with
> uninvolved columns omitted):

Well, I was hoping I could duplicate the problem, but I can't; which
means either that it's been fixed since 8.1.4 (but I don't see anything
very relevant-looking in the CVS logs) or that there's some critical
factor we haven't identified about your table definitions.

Could you send me the full "pg_dump -s" output for the two tables?
And/or a stack trace from the point of the errfinish() call?  (Note
there will be two errfinish calls, one to print the NOTICE and one
to print the ERROR --- we want to know where the second one comes from.)

            regards, tom lane