On 8/13/2007 2:48 PM, Andrew Hammond wrote:
> The table(s) you are trying to drop has a FK reference to a table which 
> is replicated. The replicated table on the subscriber has a trigger to 
> implement the foreign key, but as it is a subscriber, that trigger has 
> been disabled and the slony "don't touch" trigger is in place instead. 
> When you attempt to drop the table, the database tries to remove the FK 
> trigger, which fires the "don't touch" trigger causing everything to break.

Not quite.

What causes this to fail is that the foreign key triggers on the 
referenced and still replicated table are "parked" by slony on its 
index. The DROP TABLE tries to remove those triggers from the PK table 
and fails due to this system catalog corruption.

You have to perform the DROP TABLE through EXECUTE SCRIPT. In this case 
you probably want to use the EXECUTE ONLY ON option.


Jan


> 
> There are two ways you can drop the unsubscribed table(s):
> 1) re-add the table on the origin and use a slonik execute ddl to drop 
> it everywhere.
> 2) muck around with internal slony functions.
> 
> If you _must_ do it the second way (although I can't imagine why you'd 
> need to, and you certainly don't want to) then please email back to the 
> list for more detailed instructions.
> 
> Andrew
> 
> 
> On 8/13/07, *Richard Yen* <dba at richyen.com <mailto:dba at richyen.com>> wrote:
> 
>     Sorry, I think we're not on the same page re. this issue.
> 
>      > The *right* way to drop a table from replication is to first of all
>      > run the slonik "SET DROP TABLE" command against the table.
>      >
>     I have in fact run SET DROP TABLE on the table prior to attempting to
>     drop the table from schema.  The table (new_gm_qm_set) doesn't show
>     up in sl_table:
> 
>     mydb=# select * from _myslony.sl_table where tab_relname =
>     'new_gm_qm_set';
>     tab_id | tab_reloid | tab_relname | tab_nspname | tab_set |
>     tab_idxname | tab_altered | tab_comment
>     --------+------------+-------------+-------------+---------
>     +-------------+-------------+-------------
>     (0 rows)
> 
>      > When that is processed, on the origin, the "logtrigger" trigger gets
>      > removed.
>      > When that is processed, on subscribers, the "denyaccess" trigger gets
>      > removed, and the constraints get fixed up.
>      >
>     The logtrigger on the desired table for deletion (new_gm_qm_set ) is
>     already gone:
>     mydb=# \d new_gm_qm_set;
>                                   Table "public.new_gm_qm_set"
>           Column     |  Type   |                         Modifiers
>     ----------------+---------
>     +------------------------------------------------------------
>     id             | integer | not null default nextval
>     ('new_gm_qm_set_id_seq'::regclass)
>     owner          | integer |
>     [blah blah blah...]
>     Indexes:
>          "new_gm_qm_set_pkey" PRIMARY KEY, btree (id)
>     Foreign-key constraints:
>          "new_gm_qm_set_owner_fkey" FOREIGN KEY ("owner") REFERENCES
>     m_user(id)
> 
>     However, when I go to delete my desired table (new_gm_qm_set), it
>     complains about another table:
>     mydb=# begin; alter table new_gm_qm_set drop constraint
>     new_gm_qm_set_owner_fkey;
>     BEGIN
>     ERROR:  "m_user_pkey" is an index
> 
> 
> 
>     --Richard
> 
> 
>     _______________________________________________
>     Slony1-general mailing list
>     Slony1-general at lists.slony.info <mailto:Slony1-general at lists.slony.info>
>     http://lists.slony.info/mailman/listinfo/slony1-general
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #