On Thu, 2008-07-10 at 17:29 -0400, Andrew Sullivan wrote:
> On Thu, Jul 10, 2008 at 09:42:28AM +0800, Ow Mun Heng wrote:
> > Then I checked to see if the DDL will run on the slave as is. 
> 
> If you check the Slony admin manual, it actually suggests that you do this
> on the target machines, wrapped in BEGIN;…;ROLLBACK.  This is because it's
> very easy with execute script to run into the problem you just did.  See
> section 15.2.

The issue with the begin/rollback line isn't that it's extra work, it's
just extra time wasted to test it out. (well, better safe than sorry in
most instances)

> > BTW, at the risk of sounding like a broken record, this _really_ can't
> > be executed independent of slony? That would be the best case I would
> > say.
> 
> Here's why it has to be done with Slony's knowledge: suppose you make a
> change to the schema on the data origin at time _t_, and a change to the
> schema on a replica at time _t_+2.  Now, suppose you have a transaction that
> gets replicated at _t_+1.  The origin has the schema change, and Slony
> doesn't know that the schema on the replica isn't ready for the data.  So
> it will try to replicate that data, and things will break.
> 

This I wholly understand the implication. But again, it depends on the
situation, and in this situation, the initial population of the end
tables are via some loading mechanism which at the very beginning is
still only permitting input of varchar(4), hence anything beyond will
still be varchar(4) no matter what the update time is on master or
slave.

If I'm not mistaken, there would be no breakage as long as I don't mess
with the loading mechanism to permit input of varchar(5) data.

Anyway, I've found a way to do this independent of slony which would
perhaps interest readers here. (but whether it will break or otherwise,
it remains to be seen. It's a calculated risk I'm taking)


[quote Mario Weilguni from postgresql perform mailing list]
Example:
{OLDLEN} = 4
{NEWLEN} = 60

update pg_attribute
   set atttypmod={NEWLEN}+4
 where attname='the-name-of-the-column'
   and attrelid=(select oid from pg_class where 
relname='the-name-of-the-table')
   and atttypmod={OLDLEN}+4;

[/quote]

This is a 2 sec / table change. I change all the end-result tables
before I change the loading table to permit varchar(5) to go into the
loading table.


RAW (varchar(5)) -> loading table (varchar(4)) -> end table (varchar(4))

becomes

RAW (varchar(5)) -> loading table (varchar(4)) -> end table (varchar(5))

becomes

RAW (varchar(5)) -> loading table (varchar(5)) -> end table
(varchar(5))

DONE.