[OPEN-ILS-DEV] ***SPAM*** Re: ***SPAM*** Replication-friendly default DB setup

[Mike Rylander]

On Tue, Apr 13, 2010 at 1:56 PM, Brandon W. Uhlman
<brandon at branflakes.net> wrote:
> Hello, all.
>
> The database scripts Evergreen currently ships with are not
> replication-friendly for use with Slony-I, a well-known PostgreSQL
> replication tool used in larger Evergreen consortia (specifically, I know it
> is used at Georgia PINES and BC Sitka). Specifically, Slony stalls
> replication if you issue the TRUNCATE command, which is included in reporter
> schema as part of reporter.enable_materialized_simple_record_trigger(), and
> occasionally as part of upgrade scripts, particularly the
> soon-to-be-released 1.6.0.3-1.6.0.4 upgrade script for
> money.materialized_billable_xact_summary.
>
> For any table $foo being emptied, the best practice is to issue
>  DELETE FROM {$foo};
> before issuing
>  TRUNCATE {$foo};
>
> which cleans up the issue -- which has to do with TRUNCATE not being issued
> to the slave servers, and thus causing primary key collisions when trying to
> rebuild.
>
> If there's general support for making the default configuration
> Slony-friendly, I can submit a simple patch that issues appropriate DELETE
> commands before any occurence of TRUNCATE. The downside is that DELETE is
> significantly slower than TRUNCATE for the majority of Evergreen libraries
> who do not require the scripts to be Slony-friendly.
>
> Thoughts?

Unfortunately, supporting Slony is more complicated than that.  Any
structural changes to replicated tables have to be applied through
supporting Slony scripts, so the upgrade of a replicated setup will
almost always require hand-tooling.

I'm not against supplying a DELETE version of the trigger function,
though, as that will avoid future missteps.

--miker

>
> Brandon
>
> =============================================
> Brandon W. Uhlman
> Lillooet, BC   V0K 1V0
>
> brandon at branflakes.net
>
> ----------------------------------------------------------------
> This message was sent using IMP, the Internet Messaging Program.
>
>



-- 
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  miker at esilibrary.com
 | web:  http://www.esilibrary.com