On Wed, 2008-07-09 at 11:11 +0800, Ow Mun Heng wrote:
> On Mon, 2008-07-07 at 14:45 -0400, Andrew Sullivan wrote:
> > On Mon, Jul 07, 2008 at 04:15:24PM +0800, Ow Mun Heng wrote:
> > > individually on each slave/master table or I have to do it via slony via
> > > EXECUTE script?
> > 
> > You have to do it via execute script.
> 
> There is absolutely no other method? (how bout a second opinion just to
> re-affirm the inevitable)
> I really dread doing it via slony as this is really take a _long_ time
> and tables will be locked.

[Just for the list to Digest as a good FYI to watch out for in case you
happen to want to do the same thing]

History:
I took the plunge and did it via execute script on the master.
The DDL completed fine on the master, but nothing is happening on the
slave. Nothing is moving and there has not been any updates to any of
the other replicated tables.

In the master's slong I keep seeing

[log]
2008-07-10 09:19:57 MYT DEBUG2 remoteListenThread_1: queue event
1,1442800 SYNC
2008-07-10 09:19:57 MYT DEBUG2 remoteWorker_event: ignore new events due
to shutdown
2008-07-10 09:19:57 MYT DEBUG2 remoteListenThread_1: queue event
1,1442801 SYNC
2008-07-10 09:19:57 MYT DEBUG2 remoteWorker_event: ignore new events due
to shutdown


restarting the slon daemon and putting the logs somewhere I see
2008-07-10 09:20:49 MYT INFO   prepared for DDL script
2008-07-10 09:20:49 MYT CONFIG remoteWorkerThread_1: DDL request with 2
statements
2008-07-10 09:20:49 MYT CONFIG remoteWorkerThread_1: DDL Statement 0:
[alter table xmms.d_trh_dlu_rfpe

alter media_dcm type character varying,
alter head_dcm type character varying,
alter preamp_dcm type character varying;]
2008-07-10 09:20:49 MYT ERROR  DDL Statement failed - PGRES_FATAL_ERROR
2008-07-10 09:20:49 MYT DEBUG2 slon_retry() from pid=14545
2008-07-10 09:20:49 MYT DEBUG1 slon: retry requested
2008-07-10 09:20:49 MYT DEBUG2 slon: notify worker process to shutdown
2008-07-10 09:20:49 MYT DEBUG1 syncThread: thread done
2008-07-10 09:20:49 MYT DEBUG1 cleanupThread: thread done
2008-07-10 09:20:49 MYT DEBUG1 main: scheduler mainloop returned
2008-07-10 09:20:49 MYT DEBUG2 main: wait for remote threads
2008-07-10 09:20:49 MYT DEBUG2 sched_wakeup_node(): no_id=1 (0 threads +
worker signaled)
2008-07-10 09:20:49 MYT DEBUG1 localListenThread: thread done
2008-07-10 09:20:49 MYT DEBUG2 remoteListenThread_1: queue event
1,1435029 SYNC
2008-07-10 09:20:49 MYT DEBUG2 remoteWorker_event: ignore new events due
to shutdown
2008-07-10 09:20:49 MYT DEBUG2 remoteListenThread_1: queue event
1,1435030 SYNC
[/log]

I checked the usual problems, is it due to path issues etc. meaning, the
log-ged in slony user is having a different path (eg: show search_path;)

Nope.

Then I checked to see if the DDL will run on the slave as is. 

[execute via pgadmin]
begin;
alter table xx alter yyy type sss
[/xecute via pgadmin]

errors present in pgadmin

[log]
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view xmms.v_dlu_rfpe_raw depends on column
"media_dcm"
********** Error **********
ERROR: cannot alter type of a column used by a view or rule
SQL state: 0A000
Detail: rule _RETURN on view xmms.v_dlu_rfpe_raw depends on column
"media_dcm"
[/log]

I _did_ remember to remove the view from the master (not via execute
script though).
So, that's just FYI.

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.