Thread: 2 problems
Hello,
I am running PostgreSQL 8.1.0 on Gentoo Linux, DB is replicated with Slon
version 1.2.0.
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.
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;)
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);
Can anyone help us?
thx
Lukas
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
On Nov 15, 2007 6:04 AM, Lukas <lukas@fmf.vtu.lt> wrote:
Bad idea, if you want to use the 8.1 series, use the latest, 8.1.10
I don't know how you decided on Gentoo as a platform for your database. It strikes me as a stupid choice.
Once again, bad idea. For new clusters, you should always use the latest stable release, currently that is 1.2.12.
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?
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.
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
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
Hello, and thanks for answer,
>> 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.
In two sentences, why?
>> 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.
Yes, I agree. One question: can I use different slon versions on different
nodes in one replica?
>
>
>> 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?
Yes, all sets are on my replica.
> 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.
Yes, I agree, it just a try for work around.. Can you suggest something?
Lukas
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
On Nov 15, 2007 11:00 AM, Lukas <lukas@fmf.vtu.lt> wrote:
1) While it may be possible to use Gentoo in some fashion to achieve stability, it will always be associated with ricer performance tweaking via the random application of experimental CFLAGs, particularly when contrasted with for example debian or RHEL (if you want to stay in the Linux world).
1) People who are serious about running databases care a great deal about consistency, Gentoo fosters to opposite with their "tweak it yourself".
No. It may be possible with some non-trivial hacking, but if you care about your data enough to replicate it, then you should probably care enough to schedule appropriate downtimes to do maintenance correctly.
I'm curious how you can get a foreign key error from a subscribed table. That should be impossible since the triggers which implement foreign keys are "deactivated" by slony upon subscription. On the subscriber, using psql, please do \d+ kainos then same for padaliniu_grupes.
Did you have replication working correctly at some point? If so, please detail everything you've done (slonik scripts, any ddl) since your last known-good baseline.
Andrew
Hello, and thanks for answer,In two sentences, why?
>> 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.
1) While it may be possible to use Gentoo in some fashion to achieve stability, it will always be associated with ricer performance tweaking via the random application of experimental CFLAGs, particularly when contrasted with for example debian or RHEL (if you want to stay in the Linux world).
1) People who are serious about running databases care a great deal about consistency, Gentoo fosters to opposite with their "tweak it yourself".
>> DB is replicated with Slon version 1.2.0.> Once again, bad idea. For new clusters, you should always use the latestYes, I agree. One question: can I use different slon versions on different
> stable release, currently that is 1.2.12.
nodes in one replica?
No. It may be possible with some non-trivial hacking, but if you care about your data enough to replicate it, then you should probably care enough to schedule appropriate downtimes to do maintenance correctly.
>> Fist problem started with one node, with stopped to replicate with suchYes, all sets are on my replica.
>> 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?
I'm curious how you can get a foreign key error from a subscribed table. That should be impossible since the triggers which implement foreign keys are "deactivated" by slony upon subscription. On the subscriber, using psql, please do \d+ kainos then same for padaliniu_grupes.
> PostgreSQL uses unique indexes to implement primary keys. But as I> mentionedYes, I agree, it just a try for work around.. Can you suggest something?
> before, making changes to your schema is probably not the right way to
> solve
> your problem.
Did you have replication working correctly at some point? If so, please detail everything you've done (slonik scripts, any ddl) since your last known-good baseline.
Andrew