Jaime Casanova <jcasanov at systemguards.com.ec> writes:
> 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?
> so what are my options (besides to blame the customer for doing that)?
> set drop table, change, readd it using a merge?

Good question.

What's done sure ought to involve EXECUTE SCRIPT; the case where I'd
expect things to break down would be where you're changing from one
candidate-primary-key (e.g. - not *really* a primary key, just a
surrogate) to another.

For that scenario, I'd like to have a stored function to help enable
changing the PK.

I know we've had cases at Afilias where we weren't using the real PK; it
sure would be nice to be able to do an execute script consisting of
something like:

  select _slony1_cluster.fix_primary_key(t.tab_id) from
    _slony1_cluster.sl_table t, pg_catalog.pg_class ci, pg_catalog.pg_index i
    where
       i.indrelid = t.tab_reloid and i.indisprimary and 
       ci.oid = i.indexrelid and ci.relname <> tab_idxname;
 

fix_primary_key(tab_id) would be a function that updates sl_table to
change the index used to be the actual primary key, eliminating the use
of some other candidate primary key.

The idea here is to look at all replicated tables and change them to use
the new primary key.  Doing it inside an EXECUTE SCRIPT script makes
sure it hits all nodes consistently.

In the absence of the function, you could do it more directly by having
a bit of SQL that alters sl_table to point to the new index name, and
running that via EXECUTE SCRIPT.

Irrespective of the amount of hackery involved, it's still good to use
EXECUTE SCRIPT, as that ensures that the change takes place in between
SYNCs, so that old data can be using the old candidate PK and newer data
(after the EXECUTE SCRIPT) cleanly changes to reference the new PK.
-- 
output = ("cbbrowne" "@" "ca.afilias.info")
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"