Slony1 PostgreSQL Replication - DROP TABLE and foreign key

Author

DROP TABLE and foreign key

Ian Burrell

2005-07-20, 8:25 pm

We ran into some weirdness applying a schema change to the slave
databases. The change was to DROP a table which was not replicated
but had a foreign key referenceto a table which is replicated. It
worked fine on the master database but broke on the slave databases.

vodlive_slave1=3D# drop table mc_today_xtns
ERROR: "pk_mc_subscribers" is an index

My suspicion was that it was related to the foreign key from
mc_today_xtns to mc_subscribers.

" fk_mc_today_xtns_mc_
subscriber" FOREIGN KEY (mc_subscriber_no)
REFERENCES mc_subscribers(mc_su
bscriber_no) DEFERRABLE

Dropping the foreign key failed:

vodlive_slave1=3D# alter table mc_today_xtns drop constraint
fk_mc_today_xtns_mc_
subscriber ;
ERROR: "pk_mc_subscribers" is an index

I think what is happening is that slony has removed the triggers for
the foreign key on mc_subscribers:

voddev_slave1=3D# select relname, pg_trigger.* from pg_trigger join
pg_class on tgrelid =3D pg_class.oid where relname =3D 'mc_subscribers' ;
relname | tgrelid | tgname | tgfoid |
tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid |
tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs
----------------+----------+-------------------------+----------+--------+-=
----------+----------------+--------------+---------------+--------------+-=
---------------+---------+--------+---------------
mc_subscribers | 28724489 | _vodslony_denyaccess
_65 | 45139590 | 31 |
t | f | | 0 | f | f=20
| 1 | | _vodslony\000

On the master, there are triggers for all of the foreign key
constraints which point to mc_subscribers.

I would think removing the foreign key triggers on the slaves is the
wrong thing to do. Should we remove the foreign keys on the slave
databases?

- Ian

Jan Wieck

2005-07-20, 8:25 pm

Due to the way Slony disables triggers in combination how foreign keys
are implemented in PostgreSQL, you would have to do that drop table
through slonik's EXECUTE SCRIPT functionality as well.

Jan

On 7/20/2005 3:24 PM, Ian Burrell wrote:

> We ran into some weirdness applying a schema change to the slave
> databases. The change was to DROP a table which was not replicated
> but had a foreign key referenceto a table which is replicated. It
> worked fine on the master database but broke on the slave databases.
>
> vodlive_slave1=# drop table mc_today_xtns
> ERROR: "pk_mc_subscribers" is an index
>
> My suspicion was that it was related to the foreign key from
> mc_today_xtns to mc_subscribers.
>
> " fk_mc_today_xtns_mc_
subscriber" FOREIGN KEY (mc_subscriber_no)
> REFERENCES mc_subscribers(mc_su
bscriber_no) DEFERRABLE
>
> Dropping the foreign key failed:
>
> vodlive_slave1=# alter table mc_today_xtns drop constraint
> fk_mc_today_xtns_mc_
subscriber ;
> ERROR: "pk_mc_subscribers" is an index
>
> I think what is happening is that slony has removed the triggers for
> the foreign key on mc_subscribers:
>
> voddev_slave1=# select relname, pg_trigger.* from pg_trigger join
> pg_class on tgrelid = pg_class.oid where relname = 'mc_subscribers' ;
> relname | tgrelid | tgname | tgfoid |
> tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid |
> tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs
> ----------------+----------+-------------------------+----------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+---------------
> mc_subscribers | 28724489 | _vodslony_denyaccess
_65 | 45139590 | 31 |
> t | f | | 0 | f | f
> | 1 | | _vodslony\000
>
> On the master, there are triggers for all of the foreign key
> constraints which point to mc_subscribers.
>
> I would think removing the foreign key triggers on the slaves is the
> wrong thing to do. Should we remove the foreign keys on the slave
> databases?
>
> - Ian
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Sponsored Links