Slony1 PostgreSQL Replication - Slony crash during activation of mirror -- my

Author

Slony crash during activation of mirror -- my

Adam Haberlach

2005-05-13, 8:24 pm

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 recruitmentinterview
s_clickid_fkey on table
recruitmentinterview
s 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_fk
ey" 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_tes
t', '2', 'kvvvvv')

Christopher Browne

2005-05-13, 8:24 pm

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 recruitmentinterview
s_clickid_fkey on table
> recruitmentinterview
s 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_fk
ey" 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_tes
t', '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 & #91;yourdatabasename
]
pg_dump -s -t campaigns & #91;yourdatabasename
]

That would be rather useful for tracing what broke.

Thanks in advance!

Sponsored Links