On 4/26/2010 1:29 PM, Jaime Casanova wrote:
> 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?

Right. Your DDL either creates a new index with that same name, but 
eventually using different column(s), or your DDL script must also 
correct sl_table.

Slony doesn't actually enforce that the index used is actually one that 
implements the PK constraint. It doesn't even check if the columns are 
defined NOT NULL. Note however that actual null values will screw up 
replication, so having them declared NOT NULL is a good idea.

There actually is a good reason not to have any primary key at all. 
REINDEX cannot rebuild the PK index without an exclusive lock on the 
table, which prevents online maintenance. Slony and every application 
will be happy with any UNIQUE NOT NULL column set.


Jan

> 
> 
>> 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
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general


-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin