On Mon, Apr 26, 2010 at 11:46 AM, Melvin Davidson <melvin6925 at yahoo.com> wrote:
>
> >Seems like a customer wants to change the PK of a replicated table...
> >and from what i understood in the docs we shouldn't be using EXECUTE
> >SCRIPT for that... i'm right?
>
> Wrong! You do want to use EXECUTE SCRIPT. That insures the changed is propagated on all slaves at the same time. Just
> keep in mind that EXECUTE SCRIPT will lock all tables in a set.

Ah! [re reading http://www.slony.info/documentation/ddlchanges.html]

in 17.1. Changes that you might not want to process using EXECUTE SCRIPT, says:
"""
Slony-I stores the "primary index" name in sl_table, and uses that
name to control what columns are considered the "key columns" when the
log trigger is attached. It would be plausible to drop that index and
replace it with another primary key candidate, but changing the name
of the primary key candidate would break things.
"""

which clearly states that i'm wrong, what i shouldn't be doing is
changing the name of the index i'm using for implementing the PK
constraint, so i should put the name of the old index to the new one
for this to work?


> The big question is why is the primary key changing? If it's because the customer mistakenly wants to change the default sort
> order, or lookup, then it is best to just create another index on the tables.
>

i'm still in the process of forcing them to tell me what they want to do...
but i suspect that what is happening is that they're getting an error
because they try to insert a null in the PK (they have a lot of those,
while the pk is a sequence of numbers they didn't use serial when
created it) what they want to do now, i guess, is to create a new pk
that is a serial and allow the old pk column to accept nulls so if
they send a value or a null it's meaningless (actually that is what i
see in the script they want to execute)...
if that is true then a better solution is 1) fix the application
(which probably they won't) or 2) create a new sequence for the pk and
a before trigger that force the value of the pk to nextval(of the
sequence)

i know 1) is the best and 2) a kluge but...

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157