Fixing up log shipping support for "subscription

I discovered yesterday that a couple of changes were needed to this
patch to accomodate log shipping...

1.  Change to remote_worker.c

(I don't have a reasonable "diff of a diff"; this should be clear
enough, particularly as it's only a few lines of change)

@@ -2778,7 +2810,10 @@
if (archive_dir) {
 slon_log(SLON_DEBUG4
, "start log ship copy of %s\n",
tab_fqname);
slon_mkquery(&query1,
-                     "delete from %s;copy %s from stdin;", tab_fqname,
tab_fqname);
+                     "select %s. disableSubscriptionI
ndexes('%s'); "
+                     "delete from %s; "
+                     "copy %s from stdin;",
+                     rtcfg_namespace, tab_fqname, tab_fqname, tab_fqname);
rc =  submit_query_to_arch
ive(&query1);
if (rc < 0) {
 slon_log(SLON_ERROR,
 " remoteWorkerThread_d
: "

2.  Need enable/ disableSubscriptionI
ndexes() in the initial dump...

RCS file: /usr/local/cvsroot/slony1/slony1-engine/tools/slony1_dump.sh,v
retrieving revision 1.1
diff -c -u -r1.1 slony1_dump.sh
cvs diff: conflicting specifications of output style
--- slony1_dump.sh    17 Feb 2005 06:59:05 -0000    1.1
+++ slony1_dump.sh    28 Jul 2005 18:47:38 -0000
@@ -155,6 +155,48 @@
end;
' language plpgsql;

+create or replace function $clname. disableSubscriptionI
ndexes(text)
+returns bigint
+as '
+declare
+        p_fqname        alias for \$1;
+begin
+        update pg_catalog.pg_class set relhasindex = ''f'' from
$clname.sl_table
+                where  pg_catalog.pg_class.oid =
$clname.sl_table.tab_reloid
+                and $clname.slon_quote_input(''"'' ||
$clname.sl_table.tab_nspname || ''"."'' || $clname.sl_table.tab_relname
|| ''"'') = ''p_fqname'';
+        return 1;
+end;
+' language plpgsql;
+
+create or replace function $clname. enableSubscriptionIn
dexes(text)
+returns bigint
+as '
+declare
+    p_fqname    alias for \$1;
+begin
+    update pg_catalog.pg_class set relhasindex = ''t'' from
$clname.sl_table
+        where  pg_catalog.pg_class.oid = $clname.sl_table.tab_reloid
+        and $clname.slon_quote_input(''"'' ||
$clname.sl_table.tab_nspname || ''"."'' || $clname.sl_table.tab_relname
|| ''"'') = ''p_fqname'';
+    return 1;
+end;
+' language plpgsql;
+
+create or replace function $clname. maintenance_work_mem
(text)
+returns text
+as '
+declare
+         p_maintenance_work_m
em          alias for \$1;
+         v_old_maintenance_wo
rk_mem      text;
+begin
+        select  current_setting(''va
cuum_mem'') into
 v_old_maintenance_wo
rk_mem;
+        if  p_maintenance_work_m
em > 0 then
+        perform  set_config(''vacuum_
 mem'',p_maintenance_
work_mem,''t'');
+    end if;
+        return  v_old_maintenance_wo
rk_mem;
+end;
+' language plpgsql;
+
+
_EOF_

On Thursday 28 July 2005 11:49, Christopher Browne  wrote:
> I discovered yesterday that a couple of changes were needed to this
> patch to accomodate log shipping...
>
> 1.  Change to remote_worker.c
>
> (I don't have a reasonable "diff of a diff"; this should be clear
> enough, particularly as it's only a few lines of change)
>
> @@ -2778,7 +2810,10 @@
>          if (archive_dir) {
>               slon_log(SLON_DEBUG4
, "start log ship copy of %s\n",
> tab_fqname);
>              slon_mkquery(&query1,
> -                     "delete from %s;copy %s from stdin;", tab_fqname,
> tab_fqname);
> +                     "select %s. disableSubscriptionI
ndexes('%s'); "
> +                     "delete from %s; "
> +                     "copy %s from stdin;",
> +                     rtcfg_namespace, tab_fqname, tab_fqname, tab_fqname)
;
>              rc =  submit_query_to_arch
ive(&query1);
>              if (rc < 0) {
>                   slon_log(SLON_ERROR,
 " remoteWorkerThread_d
: "
>
> 2.  Need enable/ disableSubscriptionI
ndexes() in the initial dump...

There is a problem with the slony1-dump patch as youhave provided, it has no
logic to load the correct functions depending on PostgreSQL version.  I thin
k
we may have to rethink how slony1_diump.sh works both to be able to deal wit
h
the above version logic, as well as prevent code duplication.

One thing we could do in the interim is to only disable indexes during
conventional slony operation, and ignore that during logshipping mode.

>
> RCS file: /usr/local/cvsroot/slony1/slony1-engine/tools/slony1_dump.sh,v
> retrieving revision 1.1
> diff -c -u -r1.1 slony1_dump.sh
> cvs diff: conflicting specifications of output style
> --- slony1_dump.sh    17 Feb 2005 06:59:05 -0000    1.1
> +++ slony1_dump.sh    28 Jul 2005 18:47:38 -0000
> @@ -155,6 +155,48 @@
>  end;
>  ' language plpgsql;
>
> +create or replace function $clname. disableSubscriptionI
ndexes(text)
> +returns bigint
> +as '
> +declare
> +        p_fqname        alias for \$1;
> +begin
> +        update pg_catalog.pg_class set relhasindex = ''f'' from
> $clname.sl_table
> +                where  pg_catalog.pg_class.oid =
> $clname.sl_table.tab_reloid
> +                and $clname.slon_quote_input(''"'' ||
> $clname.sl_table.tab_nspname || ''"."'' || $clname.sl_table.tab_relname
>
> || ''"'') = ''p_fqname'';
>
> +        return 1;
> +end;
> +' language plpgsql;
> +
> +create or replace function $clname. enableSubscriptionIn
dexes(text)
> +returns bigint
> +as '
> +declare
> +    p_fqname    alias for \$1;
> +begin
> +    update pg_catalog.pg_class set relhasindex = ''t'' from
> $clname.sl_table
> +        where  pg_catalog.pg_class.oid = $clname.sl_table.tab_reloid
> +        and $clname.slon_quote_input(''"'' ||
> $clname.sl_table.tab_nspname || ''"."'' || $clname.sl_table.tab_relname
>
> || ''"'') = ''p_fqname'';
>
> +    return 1;
> +end;
> +' language plpgsql;
> +
> +create or replace function $clname. maintenance_work_mem
(text)
> +returns text
> +as '
> +declare
> +         p_maintenance_work_m
em          alias for \$1;
> +         v_old_maintenance_wo
rk_mem      text;
> +begin
> +        select  current_setting(''va
cuum_mem'') into
>  v_old_maintenance_wo
rk_mem;
> +        if  p_maintenance_work_m
em > 0 then
> +        perform  set_config(''vacuum_
 mem'',p_maintenance_
work_mem,''t'');
> +    end if;
> +        return  v_old_maintenance_wo
rk_mem;
> +end;
> +' language plpgsql;
> +
> +
>  _EOF_

>
>
>  ____________________
 ____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759

Darcy Buskermolen wrote:

>On Thursday 28 July 2005 11:49, Christopher Browne wrote:
>
> 
>
>There is a problem with the slony1-dump patch as youhave provided, it has n
o
>logic to load the correct functions depending on PostgreSQL version.  I thi
nk
>we may have to rethink how slony1_diump.sh works both to be able to deal wi
th
>the above version logic, as well as prevent code duplication.
>
>One thing we could do in the interim is to only disable indexes during
>conventional slony operation, and ignore that during logshipping mode.
>
>
Essentially, that turns disable/ enableSubscriptionIn
dices() into NOP (no
operation) routines, which, while "performance pessimal," isn't the
worst thing we could possibly do.

If we can put in a decent default in slony1-dump, and perhaps warn
people that if they are running a too-{old|new} version of PostgreSQL,
they may have to replace those functions, that's also not the worst
possible thing to do.

I'd be keener on having a PG-version-specific set of enable/disable
functions; that's an even better improvement to the patch :-).

On Tuesday 23 August 2005 14:51, Christopher Browne  wrote:
> Darcy Buskermolen wrote: 
>
> Essentially, that turns disable/ enableSubscriptionIn
dices() into NOP (no
> operation) routines, which, while "performance pessimal," isn't the
> worst thing we could possibly do.
>
> If we can put in a decent default in slony1-dump, and perhaps warn
> people that if they are running a too-{old|new} version of PostgreSQL
,
> they may have to replace those functions, that's also not the worst
> possible thing to do.
>
> I'd be keener on having a PG-version-specific set of enable/disable
> functions; that's an even better improvement to the patch :-).

Yes that would be the better solution I agree, I just don't have adequate ti
me
to put into doing that ATM. What ever solution is finalized on I'd like to
see it avoid code duplication to make developer maintainace less.

>  ____________________
 ____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759