On November 19, 2004 12:27 pm, Christopher Browne wrote:
> Darcy Buskermolen <darcy at wavefire.com> writes:
> > On November 18, 2004 07:48 am, Christopher Browne wrote:
> >>I have the following as somewhat "version-unaware" upgrade scheme
> >>which can cope with the case of needing to add a column to a table:
> >>
> >> create or replace function @NAMESPACE at .upgrade_sl_node () returns bool
> >> as '
> >> DECLARE
> >> ? ? v_row ?record;
> >> BEGIN
> >> ? ?if add_missing_table_field(@NAMESPACE@, ''sl_node'', ''no_spool'',
> >> ''boolean'') then alter table @NAMESPACE at .sl_node
> >> ? ? ? ? ? ? alter column no_spool set default = ''f'';
> >> ? ? ? ? update @NAMESPACE at .sl_node set no_spool = ''f'';
> >> ? ? ? ? return ''t'';
> >> ? ?end if;
> >> ? ?return ''t'';
> >> END;' language plpgsql;
> >>
> >> comment on function @NAMESPACE at .upgrade_sl_node() is
> >> ? 'Schema changes required to upgrade to version 1.1';
> >>
> >> create or replace function @NAMESPACE at .add_missing_table_field (text,
> >> text, text, text) returns bool as '
> >> DECLARE
> >> ? p_namespace alias for $1;
> >> ? p_table ? ? alias for $2;
> >> ? p_field ? ? alias for $3;
> >> ? p_type ? ? ?alias for $4;
> >> ? v_row ? ? ? record;
> >> ? v_query ? ? text;
> >> BEGIN
> >> ? select 1 into v_row from pg_namespace n, pg_class c, pg_attribute a
> >> ? ?where n.nspname = p_namespace and
> >> ? ? ? ? ?c.relname = n.oid and
> >> ? ? ? ? ?c.relname = p_table and
> >> ? ? ? ? ?a.attrelid = c.oid and
> >> ? ? ? ? ?a.attname = p_field;
> >> ? if not found then
> >> ? ? raise notice ''Upgrade table %.% - add field %'', p_namespace,
> >> p_table, p_field; v_query := ''alter table "'' || p_namespace || ''".''
> >> || p_table
> >>
> >> || ' add column ''; v_query := v_query || p_field || '' type '' ||
> >> || p_type '';''; execute v_query;
> >>
> >> ? ? return ''t'';
> >> ? else
> >> ? ? return ''f'';
> >> ? end if;
> >> END;' language plpgsql;
> >
> > So far this looks good to me, looking at how you implemented
> > upgrade_sl_node in -HEAD, i see you aren't using this, nor can I
> > find anyware in slonik or the like where upgrade_sl_node is called
> > when an UPGRADE FUNCTIONS gets issued. I missing something stupid
> > here ?
>
> It's something of a "stub" function, at this point; the notion is to
> have a function there for upgrade activities to accrete into.
>
> Further, it's a bit of a "trial balloon;" if it seems sensible, then
> it's sensible to continue.  If it's not, then it should disappear...

it looks to be senseable to me, Just a matter of ironing out the details of 
how we want to handle the actual upgrade in slonik beyond just sending a 
notify _cluster.Restart.  Chris, what were your thoughts on how you figured 
this should be handled.  At the moment I'm leaning towards a function in 
slony_funcs.sql that you just serialize the placement of  perform upgrade_foo 
into?


-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com