Thread: Problem dropping table

We are using postgresql 8.2.3 with slony1 1.2.8 and we're having problems trying to drop a table on the slony1 secondary that was sucessfully dropped on the master database.

I'm sure this is just because I'm missing something, but it doesn't seem like there should be any reason the table cannot be dropped. The problem seems to be due to a foreign key constraint on the table we are trying to drop, I don't believe any other tables reference the table we are trying to drop with foreign key constraints.

The schema of the table is as follows:
   Table "public.member_note"
  Column   |  Type   | Modifiers
-----------+---------+-----------
member_id | bigint  | not null
notes_id  | bigint  | not null
notes_idx | integer | not null
Foreign-key constraints:
    "fkb5d78a7737864f78" FOREIGN KEY (member_id) REFERENCES member(id)
    "fkb5d78a77503a5452" FOREIGN KEY (notes_id) REFERENCES note(id)

When we try to drop the table we get the error:
ERROR:  "member_pkey" is an index

This error seems to have something to do with the primary key in the related member table:
                 Table "public.member"
      Column       |          Type          | Modifiers
-------------------+------------------------+-----------
discriminator     | character varying(31)  | not null
id                | bigint                 | not null
deleted           | boolean                | not null
status            | character varying(255) | not null
principal_id      | bigint                 |
extravaluelist_id | bigint                 |
Indexes:
    "member_pkey" PRIMARY KEY, btree (id)
    "member_principal_id_key" btree (principal_id)
Foreign-key constraints:
    "fk892776ba530919c4" FOREIGN KEY (extravaluelist_id) REFERENCES extravaluelist(id)
    "fk892776baa88520c6" FOREIGN KEY (principal_id) REFERENCES principal(id)


We have also tried to drop the foreign key constraint on the table as an intermediate step to getting rid of the table:
alter table member_note drop constraint fkb5d78a7737864f78;
ERROR:  "member_pkey" is an index

Anyone know how to drop this table or what the error means?

On Tuesday 18 September 2007 10:30, Ken Logan <ken@i2rd.com> wrote:
> When we try to drop the table we get the error:
> ERROR:  "member_pkey" is an index

You have to remove the table from it's Slony set before you can drop it.
Slony does some hackish things to subscriber tables that make them
unusable for normal DDL operations.

Your master probably isn't too thrilled, either, since it probably
thinks the table is still in a set.

In any case, you'd probably have better luck with this on the Slony
list. I'm not at all sure your problem can be fixed without discarding
the slave, but someone there might know.

--
"Corruptissima republica, plurimae leges" (The more corrupt the state,
the more laws.) - Tacitus

On Tue, 2007-09-18 at 11:10 -0700, Alan Hodgson wrote:

On Tuesday 18 September 2007 10:30, Ken Logan <ken@i2rd.com> wrote:
> When we try to drop the table we get the error:
> ERROR:  "member_pkey" is an index

You have to remove the table from it's Slony set before you can drop it. 
Slony does some hackish things to subscriber tables that make them 
unusable for normal DDL operations.

Your master probably isn't too thrilled, either, since it probably 
thinks the table is still in a set.

We actually used slonik_drop_table so its been properly removed from slony.

In any case, you'd probably have better luck with this on the Slony 
list. I'm not at all sure your problem can be fixed without discarding 
the slave, but someone there might know.

I was afraid of that... since there doesn't seem to be any remnants left behind by slony affecting this table I'm not sure I can ask a sensible question about it on the slony list, so I guess I'll need to either dig into the source for what the postgresql error message means or reinit the slave (again).