Norman Yamada wrote:
> We're running into a strange problem with slony. We have a fairly  
> simple slony set up, one master node, two slave nodes, all running  
> slony 1.1.5 against postgresql 8.1.4 on debian testing boxes.
>
> We tried to push a DDL change using the EXECUTE SCRIPT command on the  
> master node to alter one of the slonied tables (adding a column with  
> a foreign key constraint). The slony event failed with the following  
> output  on one of the slave nodes:
>
>
> 'alter table exchange_info add column locale varchar(25) REFERENCES  
> public.locale(city);', '-1');
> insert into "_tmc_replication".sl_confirm  (con_origin, con_received,  
> con_seqno, con_timestamp)
> values (1, 2, '359373', now()); commit transaction;"
> PGRES_FATAL_ERROR ERROR:  duplicate key violates unique constraint  
> "pg_trigger_tgrelid_tgname_index"
> CONTEXT:  SQL statement "update "pg_catalog".pg_trigger set tgrelid  
> =  $1  where tgrelid =  $2 "
>   
Cutting it down to the most vital bit, the question is why uniqueness
of  the index is getting violated.

bz at localhost:5832=# \d pg_trigger_tgrelid_tgname_index
Index "pg_catalog.pg_trigger_tgrelid_tgname_index"
 Column  | Type
---------+------
 tgrelid | oid
 tgname  | name
unique, btree, for table "pg_catalog.pg_trigger"

Apparently there is already a trigger with the same name against the table.

Take a look at the contents of pg_trigger thus, on master and the
failing subscriber:

select * from pg_trigger where tgrelid in (select oid from pg_class
where relname in 'exchange_info', 'locale');

I expect you'll discover that the subscriber already had a trigger.  How
it got there is the puzzle you'll need to figure out, and that'll
hopefully explain why it broke.  Actually, it's probably useful to
compare several nodes; master, a "slave where it worked," and "slave
where it broke."