Re: Strange error dropping foreign key

From: Hannu Krosing <hannu(at)skype(dot)net>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Pgsql-hackers(at)postgresql(dot)org
Subject: Re: Strange error dropping foreign key
Date: 2007-10-19 13:54:48
Message-ID: 1192802088.6736.3.camel@hannu-laptop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Ü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(at)hagander(dot)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 ;)

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Martijn van Oosterhout 2007-10-19 14:25:57 Re: debugger
Previous Message Magnus Hagander 2007-10-19 13:29:26 Re: Strange error dropping foreign key