Re: [Slony1-general] 2 problems

On Nov 15, 2007 6:04 AM, Lukas <lukas(at)fmf(dot)vtu(dot)lt> wrote:

> Hello,
>
> I am running PostgreSQL 8.1.0

Bad idea, if you want to use the 8.1 series, use the latest, 8.1.10

> on Gentoo Linux,

I don't know how you decided on Gentoo as a platform for your database. It
strikes me as a stupid choice.

> DB is replicated with Slon version 1.2.0.

Once again, bad idea. For new clusters, you should always use the latest
stable release, currently that is 1.2.12.

> Fist problem started with one node, with stopped to replicate with such
> error:
>
> 2007-11-15 13:17:21 EET ERROR remoteWorkerThread_1: "insert into
> "public"."kainos"
> (paslaugos,laikai,abonimentas,kaina,pastaba,rodyti,tipas,vienkartinis,gal
>
> ioja_nuo,galioja_iki,id,pradine_imoka,intervalas,intervalas_paskutinis,imoku_skaicius,viso_sumoketi,periodine_imoka,max_pirkiniu_suma,padalinys,padaliniu_gru
> pe) values ('baseinas+treniruokliai ','Visos dienos
> ','1 m<EB>nesio ','284.00','Bendra korta
> ','t','V
> isi','f',NULL,NULL,'1110',NULL,NULL,NULL,NULL,NULL,NULL,'50.00
> ','0','201');
> " ERROR: insert or update on table "kainos" violates foreign key
> constraint "fk_kainos_padaliniu_grupe"
> DETAIL: Key (padaliniu_grupe)=(201) is not present in table
> "padaliniu_grupes".
>
> Note that table "kainos" and table "padaliniu_grupes" are replicated!
> only in two different sets.

That's probably a bad idea. Unless you have a very good reason to put them
in different sets, you should keep stuff that inter-relates together in a
single set. Are both sets subscribed on your replica?

> Then I wanted to drop FK constraint "fk_kainos_padaliniu_grupe" to let
> replication goon, and then create it back, but I got error:
>
> ERROR: "padaliniu_grupes_pkey" is an index
> SQL state: 42809
> (SQL was ALTER TABLE kainos DROP CONSTRAINT fk_kainos_padaliniu_grupe;)

There are two things obviously wrong with this.

1) You're trying to drop a different constraint than the one that's
complaining.
2) You're messing around with your schema when slony expects and require
them to be identical. There can be exceptions to this rule, but to start out
with you'd better just follow it.

> Why?
> "padaliniu_grupes_pkey" is not an index!! and I do not want to drop it...
> "padaliniu_grupes_pkey" is a PK on table "padaliniu_grupes":
> ALTER TABLE padaliniu_grupes ADD CONSTRAINT padaliniu_grupes_pkey PRIMARY
> KEY(pg_id);

PostgreSQL uses unique indexes to implement primary keys. But as I mentioned
before, making changes to your schema is probably not the right way to solve
your problem.

Andrew