Adam Haberlach wrote:

>  
> First, sorry if this is either an FAQ or a common problem.  I've been
> using Postgres for quite a while, but this is the first week I've been
> using Slony.
>  
> I'm trying to replicate a database:
> Master: PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc
> (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-42)
> Slave:PostgreSQL 8.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 3.2.3 20030502 (Red Hat Linux 3.2.3-49)
> Slony: slony1-1.1.0.beta4
>
> I created a trivial example of a single table replicating to another
> and tested that.  Now I'm using a copy of a production database.  I've
> written the little slonik scripts that initialize the cluster, create
> the tableset, etc...
>
> I can fire up slon, and then when I actually subscribe the slave to
> the set, the slave's slon crashes.  It seems to be trying to drop a
> constraint, but specifying an index.  This happens for at least two
> tables (I commented one out of the tableset and tried to get past it).
>
> Here's some output:
>
> 2005-05-13 13:50:56 PDT DEBUG2 remoteWorkerThread_1: copy table
> public.clicks
> 2005-05-13 13:50:56 PDT DEBUG3 remoteWorkerThread_1: table
> public.clicks does not require Slony-I serial key
> 2005-05-13 13:50:56 PDT DEBUG2 remoteWorkerThread_1: Found 1 indices
> for public.clicks - select 'alter table public.clicks drop constraint
> ' || "pg_catalog".quote_ident(co.conname),   'alter table
> public.clicks add  ' ||  pg_get_constraintdef(co.oid)  from pg_class
> c, pg_constraint co, pg_namespace ns where   'public.clicks' =
> "pg_catalog".quote_ident(ns.nspname) || '.' ||
> "pg_catalog".quote_ident(c.relname) and  co.connamespace = ns.oid and 
> co.contype in ('p', 'u') and  c.oid = co.conrelid and  ns.oid =
> c.relnamespace UNION ALL  select 'drop index ' ||
> "pg_catalog".quote_ident(ns.nspname) || '.' ||
> "pg_catalog".quote_ident(ci.relname) || ';' ,  pg_get_indexdef(ci.oid)
> from   pg_class c, pg_namespace ns, pg_class ci, pg_index i where  
> 'public.clicks' = "pg_catalog".quote_ident(ns.nspname) || '.' ||
> "pg_catalog".quote_ident(c.relname) and  i.indrelid = c.oid and 
> i.indexrelid = ci.oid and     not exists (select * from pg_constraint
> co where connamespace = ns.oid and           conrelid = c.oid and
> contype in ('p', 'u') and co.conname = ci.relname);
> 2005-05-13 13:50:56 PDT DEBUG2 remoteWorkerThread_1: start dropping 1
> indices
> NOTICE:  constraint recruitmentinterviews_clickid_fkey on table
> recruitmentinterviews depends on index clicks_pkey
> 2005-05-13 13:50:56 PDT ERROR  remoteWorkerThread_1: "alter table
> public.clicks drop constraint clicks_pkey;" PGRES_FATAL_ERROR ERROR: 
> "clicks_pkey" is an index
> 2005-05-13 13:50:56 PDT FATAL  remoteWorkerThread_1: drop index during
> copy failed: 141071544
> 2005-05-13 13:50:56 PDT DEBUG1 slon: shutdown requested
>
> Here's the table:
>
>      Column     |            Type             |                      
> Modifiers                       
> ----------------+-----------------------------+--------------------------------------------------------
>  id             | integer                     | not null default
> nextval('public.clicks_id_seq'::text)
>  campaignid     | integer                     |
>  clicktimestamp | timestamp without time zone | not null
>  referringpage  | character varying(200)      |
>  ipaddress      | inet                        |
>  browser        | character varying(200)      |
> Indexes:
>     "clicks_pkey" PRIMARY KEY, btree (id)
> Foreign-key constraints:
>     "clicks_campaignid_fkey" FOREIGN KEY (campaignid) REFERENCES
> campaigns(id)
> Triggers:
>     _pmx_test_logtrigger_2 AFTER INSERT OR DELETE OR UPDATE ON clicks
> FOR EACH ROW EXECUTE PROCEDURE _pmx_test.logtrigger('_pmx_test', '2',
> 'kvvvvv')
>
Thanks muchly for the report; that evidently shows that there is a bug
in the query that was actually in the log that you showed...

select 'alter table public.clicks drop constraint ' ||
"pg_catalog".quote_ident(co.conname),   'alter table public.clicks add 
' ||  pg_get_constraintdef(co.oid)  from pg_class c, pg_constraint co,
pg_namespace ns where   'public.clicks' =
"pg_catalog".quote_ident(ns.nspname) || '.' ||
"pg_catalog".quote_ident(c.relname) and  co.connamespace = ns.oid and 
co.contype in ('p', 'u') and  c.oid = co.conrelid and  ns.oid =
c.relnamespace UNION ALL  select 'drop index ' ||
"pg_catalog".quote_ident(ns.nspname) || '.' ||
"pg_catalog".quote_ident(ci.relname) || ';' ,  pg_get_indexdef(ci.oid)
from   pg_class c, pg_namespace ns, pg_class ci, pg_index i where  
'public.clicks' = "pg_catalog".quote_ident(ns.nspname) || '.' ||
"pg_catalog".quote_ident(c.relname) and  i.indrelid = c.oid and 
i.indexrelid = ci.oid and     not exists (select * from pg_constraint co
where connamespace = ns.oid and           conrelid = c.oid and contype
in ('p', 'u') and co.conname = ci.relname);

The problem evidently relates to the foreign key constraint.

Would it be possible for you to send me the schema for both tables
(clicks and campaigns)?

e.g. - output of...

pg_dump -s -t clicks [yourdatabasename]
pg_dump -s -t campaigns [yourdatabasename]

That would be rather useful for tracing what broke.

Thanks in advance!