Strange error dropping foreign key

"Magnus Hagander" <magnus@hagander.net> writes:

db=# alter table isi.items_stat drop constraint items_stat_item_id_fkey;
ERROR: "items_pkey" is an index

Context please?

regards, tom lane

On Thu, Oct 18, 2007 at 10:48:02AM -0400, Tom Lane wrote:

"Magnus Hagander" <magnus@hagander.net> writes:

db=# alter table isi.items_stat drop constraint items_stat_item_id_fkey;
ERROR: "items_pkey" is an index

Context please?

Yeah, I had that one coming, didn't it... That's what I get for trying to
type it up on my phone on the way home.

Anyway. I've asked for a dump of the db to get all the details, but basically:
CREATE TABLE items (
id int not null primary key,
<bunchoffields>
);
CREATE TABLE items_stat (
item_id int not null references items.id,
<bunchoffields>
);

One thing that might be related - items is a Slony slave table. items_stat
is *not* in the Slony set. It's not *supposed* to have a foreign key to the
items table, which is why I'm trying to drop it.

It had the same scenario on teh primary slony node, but there the ALTER
TABLE to drop the constraint worked fine. Again, items is master in the
slony set, items_stat is not in any slony set.

//Magnus

Ühel kenal päeval, R, 2007-10-19 kell 15:29, kirjutas Magnus Hagander:

On Thu, Oct 18, 2007 at 10:48:02AM -0400, Tom Lane wrote:

"Magnus Hagander" <magnus@hagander.net> writes:

db=# alter table isi.items_stat drop constraint items_stat_item_id_fkey;
ERROR: "items_pkey" is an index

Context please?

Yeah, I had that one coming, didn't it... That's what I get for trying to
type it up on my phone on the way home.

Anyway. I've asked for a dump of the db to get all the details, but basically:
CREATE TABLE items (
id int not null primary key,
<bunchoffields>
);
CREATE TABLE items_stat (
item_id int not null references items.id,
<bunchoffields>
);

One thing that might be related - items is a Slony slave table. items_stat
is *not* in the Slony set. It's not *supposed* to have a foreign key to the
items table, which is why I'm trying to drop it.

Slony does strange stuff to FK-s and other constraints, at least in 1.X
versions (like changing the relation type from index to
<i-dont-remember-what>) and thus you are not supposed to do DDL directly
on slaves (or even masters).

You should use EXECUTE SCRIPT slonik command to do DDL, so that drop
index happens while original state is temporaryly restored)

Actually this should belongs to Slony list ;)

Ühel kenal päeval, R, 2007-10-19 kell 15:29, kirjutas Magnus Hagander:

On Thu, Oct 18, 2007 at 10:48:02AM -0400, Tom Lane wrote:

"Magnus Hagander" <magnus@hagander.net> writes:

db=# alter table isi.items_stat drop constraint items_stat_item_id_fkey;
ERROR: "items_pkey" is an index

Context please?

Yeah, I had that one coming, didn't it... That's what I get for trying to
type it up on my phone on the way home.

Anyway. I've asked for a dump of the db to get all the details, but basically:
CREATE TABLE items (
id int not null primary key,
<bunchoffields>
);
CREATE TABLE items_stat (
item_id int not null references items.id,
<bunchoffields>
);

One thing that might be related - items is a Slony slave table. items_stat
is *not* in the Slony set. It's not *supposed* to have a foreign key to the
items table, which is why I'm trying to drop it.

Slony does strange stuff to FK-s and other constraints, at least in 1.X
versions (like changing the relation type from index to
<i-dont-remember-what>) and thus you are not supposed to do DDL directly
on slaves (or even masters).

I'm doing DDL on a table that is NOT replicated. Are you saying I shouldn't even have unreplicated tables in the same database?

Now I shouldn't be referencing the table from an unreplicated one, but that's what I'm trying to fix!

You should use EXECUTE SCRIPT slonik command to do DDL, so that drop
index happens while original state is temporaryly restored)

1) I'm dropping a foreign key, not an index. The problem is that somehow that gets translated into trying to drop the primary key of the _other_ table.
2) The constraint doesn't exist on the master, only the slave, so I don't beleive EXECUTE SCRIPT will work, no?

Actually this should belongs to Slony list ;)

I'm not entirely convinced it's a Slony problem. Actually when I first posted I didn't even think it might be slony related.

/Magnus

2) The constraint doesn't exist on the master, only the slave, so I don't
beleive EXECUTE SCRIPT will work, no?

No, it does not work. I've tried.

// John

_________________________________________________________

John Parnefjord
Samordnare UBIT Utveckling / Coordinator UBIT Development
Karolinska Institutet Universitetsbiblioteket / University Library
8:100 (Alfred Nobels allé 8)
SE-141 83 Huddinge Sweden
+46 8 524 84 144
http://ki.se/ub
_________________________________________________________