Re: Strange error dropping foreign key - Mailing list pgsql-hackers

From Magnus Hagander
Subject Re: Strange error dropping foreign key
Date
Msg-id 200710200822070000@601911286
Whole thread Raw
In response to Strange error dropping foreign key  ("Magnus Hagander" <magnus@hagander.net>)
List 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@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
primarykey 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


pgsql-hackers by date:

Previous
From: "Gokulakannan Somasundaram"
Date:
Subject: Re: Including Snapshot Info with Indexes
Next
From: "Heikki Linnakangas"
Date:
Subject: Re: Debugger