Обсуждение: unable to drop a constraint
Hi,
I'm using postgresql version 8.3.1
I have two tables, one has a field with a foreign key pointing to the
primary key of another table. When I to drop the first table, I get
the following error:
test=# drop table user;
ERROR: "customer_pkey" is an index
test=# select * from pg_constraint where conname like '%user%';
conname | connamespace | contype | condeferrable |
condeferred | conrelid | contypid | confrelid | confupdtype |
confdeltype | confmatchtype | conkey | confkey | conpfeqop | conppeqop
| conffeqop | conbin | consrc
--------------------+--------------+---------+---------------
+-------------+----------+----------+-----------+-------------
+-------------+---------------+--------+---------+-----------
+-----------+-----------+--------+--------
user_pkey | 137354 | p | f |
f | 137475 | 0 | 0 |
| | | {1} | |
| | | |
user_role_fkey | 137354 | f | f |
f | 137475 | 0 | 137463 | a |
a | u | {2} | {1} | {93} | {93}
| {93} | |
(2 rows)
so, I try to drop the foreign key constraint:
test=# alter table user DROP CONSTRAINT user_role_fkey;
ERROR: "customer_pkey" is an index
quite frustrated, I try to drop the pk index on the customer table:
test=# drop index customer_pkey ;
ERROR: cannot drop index customer_pkey because constraint
customer_pkey on table customer requires it
HINT: You can drop constraint customer_pkey on table customer instead.
test=# ALTER TABLE customer drop CONSTRAINT customer_pkey;
ERROR: "customer_pkey" is an index
I'm quite cunfused... Any idea?
Thanks,
e.
Enrico Sirola <enrico.sirola@gmail.com> writes:
> I have two tables, one has a field with a foreign key pointing to the
> primary key of another table. When I to drop the first table, I get
> the following error:
> test=# drop table user;
> ERROR: "customer_pkey" is an index
I don't think you're being entirely truthful with us, because "user"
is a reserved word. If you'd typed exactly that, you would have
gotten
regression=# drop table user;
ERROR: syntax error at or near "user"
LINE 1: drop table user;
^
I'm not certain if this is just pilot error or if there really is
something odd going on, but obfuscating what you're doing doesn't
make it easier to decipher. Please show what you really typed and
what you really got back. psql \d output for the two tables might
be helpful too.
regards, tom lane
On 2008-07-16, at 7:18 AM, Enrico Sirola wrote: > Hi, > > I'm using postgresql version 8.3.1 > > I have two tables, one has a field with a foreign key pointing to > the primary key of another table. When I to drop the first table, I > get the following error: > > test=# drop table user; > ERROR: "customer_pkey" is an index Are you using slony perchance? This looks like an issue with slony blocking DDL operations: http://lists.slony.info/pipermail/slony1-general/2006-November/005216.html Michael Glaesemann michael.glaesemann@myyearbook.com
Hello Tom,
Il giorno 16/lug/08, alle ore 16:40, Tom Lane ha scritto:
> Enrico Sirola <enrico.sirola@gmail.com> writes:
>> I have two tables, one has a field with a foreign key pointing to the
>> primary key of another table. When I to drop the first table, I get
>> the following error:
>
>> test=# drop table user;
>> ERROR: "customer_pkey" is an index
>
> I don't think you're being entirely truthful with us, because "user"
> is a reserved word. If you'd typed exactly that, you would have
> gotten
> regression=# drop table user;
> ERROR: syntax error at or near "user"
> LINE 1: drop table user;
> ^
You are right, the table name is slightly different, anyway I simply
searched the original table name and replaced it with "user". You will
now be able to reproduce it anyway, because I can't reproduce the
behaviour myself recreating the schema on another cluster.
> I'm not certain if this is just pilot error or if there really is
> something odd going on, but obfuscating what you're doing doesn't
> make it easier to decipher. Please show what you really typed and
> what you really got back. psql \d output for the two tables might
> be helpful too.
please consider the old psql session, just s/sps_user/user and s/
sps_customer/customer
Here's what you requested:
-bash-3.1$ psql sps_test
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
sps_test=# set role sps;
SET
sps_test=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+-------
public | schema_version | table | slony
sps | enabled_customer | view | sps
sps | group_to_customer | table | sps
sps | sps_customer | table | sps
sps | sps_user | table | sps
(5 rows)
sps_test=> \d sps_user
Table "sps.sps_user"
Column | Type |
Modifiers
-------------+-----------------------------
+----------------------------------------------------------
name | character varying(32) | not null
role | name | not null
password | password |
created | timestamp(3) with time zone | not null default
(now())::timestamp(3) with time zone
valid_until | timestamp(3) with time zone | not null default
'infinity'::timestamp(3) with time zone
email | character varying(100) |
Indexes:
"sps_user_pkey" PRIMARY KEY, btree (name)
sps_test=> \d sps_customer
Table "sps.sps_customer"
Column | Type |
Modifiers
--------------+-----------------------------
+----------------------------------------------------------
name | name | not null
description | text |
ftp_site | character varying |
ftp_user | character varying |
ftp_password | character varying |
created | timestamp(3) with time zone | not null default
(now())::timestamp(3) with time zone
valid_until | timestamp(3) with time zone | not null default
'infinity'::timestamp(3) with time zone
Indexes:
"sps_customer_pkey" PRIMARY KEY, btree (name)
Thanks for your help,
e.
Hi Michael, Il giorno 16/lug/08, alle ore 18:21, Michael Glaesemann ha scritto: > > On 2008-07-16, at 7:18 AM, Enrico Sirola wrote: > >> Hi, >> >> I'm using postgresql version 8.3.1 >> >> I have two tables, one has a field with a foreign key pointing to >> the primary key of another table. When I to drop the first table, I >> get the following error: >> >> test=# drop table user; >> ERROR: "customer_pkey" is an index > > Are you using slony perchance? This looks like an issue with slony > blocking DDL operations: hmmmm I don't use slony anymore, but these tables were part of a replication set in the past.... It seems a good starting point, thanks a lot, e.