Jan Wieck <JanWieck at Yahoo.com> writes:

> On 11/17/2004 5:50 PM, Darcy Buskermolen wrote:
>
>> On November 17, 2004 01:22 pm, Pallav Kalva wrote:
>>> Darcy Buskermolen wrote:
>>> >On November 8, 2004 09:55 am, Darcy Buskermolen wrote:
>>> >>Hello,
>>> >>
>>> >>In order to apply some more improvements to the 1.1 branch of slony
>>> >> making it more pg_dump friendly. I'm wondering if there is a compelling
>>> >> reason to continue to have sl_table.tab_reloid and
>>> >> sl_sequence.seq_reloid reference pg_class.oid, instead of storing the
>>> >> fully qualified name ?
>>> >
>>> >Ok I've done some tests, and beat some code around and have pretty much
>>> >discoverd that there is no "nice" way to convert to name usage and have
>>> > ALTER TABLE foo RENAME bar work and not break replication under some
>>> > cases.
>>>
>>>     Under what replication cases does the ALTER TABLE foo RENAME bar
>>> does not work ? I am new to slony and right now I am  testing out all
>>> the scenarios, I tried to rename a table which is replicated and I could
>>> rename the table with the EXECUTE script on master and slave without any
>>> problems .  am I missing something? excuse me if this is totally
>>> unrelated .
>> It's totaly unrelated, this change is specificaly related to
>> improvements I'm planning to make to slony to make it more pg_dump
>> friendly.  Currently alter table foo rename bar works 100% as it
>> should.   This message is more related to current hackers  asking
>> what their preference would be related to extending the internal
>> tables of slony.
>
> I had discussed that matter with CBB a little. My current thoughts
> are that Slony-schema changes should be handled by a store
> procedure, which is (re)defined by the newly loaded slony1-funcs.sql
> and will be called by slon during "update functions" and will
> receive the previous schema version as an argument. That function
> then will execute all the version to version specific SQL for that
> upgrade.
>
> As said ... current thoughts ... feel free to grind them to dust.

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;

I would think that being somewhat independent of being "version number
aware" would be a good thing.  For instance, I have put the somewhat
"1.1-specific" view, sl_status, into some 1.0.x systems because it
happens to be a useful view on what various nodes are up to.

If we know that version 1.1 needs certain added columns, the "safe"
way to deal with this is to check for each one in turn, in much the
way demonstrated above, so that some not-overly-unfriendly reaction is
possible if a user has done some pre-patching.

As Jan said, "grind to dust" as appropriate...
-- 
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)