unable to drop a constraint

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.