declare
p_old alias for $1;
begin
-- upgrade sl_table
if p_old IN ('1.0.2', '1.0.5', '1.0.6') then
-- Add new column(s) sl_table.tab_relname, sl_table.tab_nspname
execute 'alter table sl_table add column tab_relname name';
execute 'alter table sl_table add column tab_nspname name';
-- populate the colums with data
update sl_table set
tab_relname = PGC.relname, tab_nspname = PGN.nspname
from pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN
where sl_table.tab_reloid = PGC.oid
and PGC.relnamespace = PGN.oid;
-- constrain the colums
execute 'alter table sl_table alter column tab_relname set NOT NULL';
execute 'alter table sl_table alter column tab_nspname set NOT NULL';
end if;
-- upgrade sl_sequence
if p_old IN ('1.0.2', '1.0.5', '1.0.6') then
-- Add new column(s) sl_sequence.seq_relname, sl_sequence.seq_nspname
execute 'alter table sl_sequence add column seq_relname name';
execute 'alter table sl_sequence add column seq_nspname name';
-- populate the columns with data
update sl_sequence set
seq_relname = PGC.relname, seq_nspname = PGN.nspname
from pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN
where sl_sequence.seq_reloid = PGC.oid
and PGC.relnamespace = PGN.oid;
-- constrain the data
execute 'alter table sl_sequence alter column seq_relname set NOT NULL';
execute 'alter table sl_sequence alter column seq_nspname set NOT NULL';
end if;
-- ----
-- Changes from 1.0.x to 1.1.0
-- ----
if p_old IN ('1.0.2', '1.0.5', '1.0.6') then
-- Add new column sl_node.no_spool for virtual spool nodes
execute 'alter table sl_node add column no_spool boolean';
update sl_node set no_spool = false;
end if;
-- ----
-- Changes for 1.1.3
-- ----
if p_old IN ('1.0.2', '1.0.5', '1.0.6', '1.1.0', '1.1.1', '1.1.2') then
-- Add new table sl_nodelock
execute 'create table sl_nodelock (
nl_nodeid int4,
nl_conncnt serial,
nl_backendpid int4,
CONSTRAINT "sl_nodelock-pkey"
PRIMARY KEY (nl_nodeid, nl_conncnt)
)';
-- Drop obsolete functions
execute 'drop function terminateNodeConnections(name)';
execute 'drop function cleanupListener()';
execute 'drop function truncateTable(text)';
end if;
-- ----
-- Changes for 1.2
-- ----
if p_old IN ('1.0.2', '1.0.5', '1.0.6', '1.1.0', '1.1.1', '1.1.2', '1.1.3') then
-- Add new table sl_registry
execute 'create table sl_registry (
reg_key text primary key,
reg_int4 int4,
reg_text text,
reg_timestamp timestamp
) without oids';
execute 'alter table sl_config_lock set without oids;';
execute 'alter table sl_confirm set without oids;';
execute 'alter table sl_event set without oids;';
execute 'alter table sl_listen set without oids;';
execute 'alter table sl_log_1 set without oids;';
execute 'alter table sl_log_2 set without oids;';
execute 'alter table sl_node set without oids;';
execute 'alter table sl_nodelock set without oids;';
execute 'alter table sl_path set without oids;';
execute 'alter table sl_seqlog set without oids;';
execute 'alter table sl_sequence set without oids;';
execute 'alter table sl_set set without oids;';
execute 'alter table sl_setsync set without oids;';
execute 'alter table sl_subscribe set without oids;';
execute 'alter table sl_table set without oids;';
execute 'alter table sl_trigger set without oids;';
end if;
return p_old;
end; |