On 7/14/2005 1:50 PM, David Parker wrote:

> Wow, I was just starting to try and improve our initial subscription
> performance in slony 1.0.2 (I know, I know....) by figuring out what
> indexes to drop, saving off appropriate create statements, etc.  - but
> your way is much cooler! It looks like we'll have the opportunity to
> upgrade to 1.1 in the next few months as part of our own app upgrade, so
> maybe I can just avoid all this work ;-)
> 
> One question: the code in the patch seems to assume that relhasindex
> should always be set back to 't' - is this safe because of the slony
> primary key requirement for replicated tables, i.e., we don't have to
> worry about setting relhasindex='t' on a table that actually had no
> indexes to begin with?

Slony strictly requires at least one unique index on every replicated 
table. So if you happen to get it to replicate a table that doesn't have 
any index at all, this adds very little to the damage already done :-)


Jan

> 
> Thanks!
> 
> - DAP 
> 
> -----Original Message-----
> From: slony1-general-bounces at gborg.postgresql.org
> [mailto:slony1-general-bounces at gborg.postgresql.org] On Behalf Of Darcy
> Buskermolen
> Sent: Thursday, July 14, 2005 12:39 PM
> To: slony1-general at gborg.postgresql.org
> Subject: Re: [Slony1-general] [PATCH] subscribe set performance
> enhancements
> 
> On Thursday 14 July 2005 09:23, Christopher Browne wrote:
>> Darcy Buskermolen wrote:
>> >Hello,
>> >
>> >I have implemented an experimental patch that avoids index 
>> >maintainance  during the SUBSCRIBE SET phase of replication.  This
> work is based apon
>> > theory and findings of Jan.   My initial testing shows that
> subscribing
>> > a large heavily hit pgbench (scalling factor of 100 with 50 
>> >concurrent
>> > clients) database can subscribe set in the same amount of time as it
> 
>> >takes to pg_dump | psql.
>> >
>> >Those of you who are interested can find the patch for 1.1 and CVS 
>> >HEAD at http://www.dbitech.ca/slony/patches/subscription-perf-patch .
> 
>> >I make no  firm claim that, at this time, this patch will act as 
>> >intended for  everybody.  It would help us in getting this patch 
>> >fully ironed out and  applied to the main tree, if those of you who 
>> >do try this patch would  report your findings back to the list, 
>> >wether it be yes it works or I got  an error.
>>
>> It didn't work as provided; the 'oid' reference in the second line of 
>> the query was ambiguous.
>>
>> Use 'where pg_catalog.pg_class.oid' instead of 'where oid' in the two 
>> updates on pg_catalog.pg_class, and that turns out better...
>>
>> +#if 1
>> +                 "update pg_catalog.pg_class set relhasindex = 'f'
>> FROM  %s.sl_table "
>> +                 "where pg_catalog.pg_class.oid =
> %s.sl_table.tab_reloid "
>> +                 "and %s.slon_quote_input('\"' ||
>> %s.sl_table.tab_nspname || '\".\"' || %s.sl_table.tab_relname || '\"')
> 
>> = '%s'; "
>> +#endif
> 
> Good catch Christopher.   I have updated the patch to reflect this
> change.
> 
> --
> Darcy Buskermolen
> Wavefire Technologies Corp.
> 
> http://www.wavefire.com
> ph: 250.717.0200
> fx: 250.763.1759
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #