Christopher Browne wrote:
> Darcy Buskermolen wrote:
>   
>> On Wednesday 12 July 2006 10:01, Rod Taylor wrote:
>>   
>>     
>>> On Wed, 2006-07-12 at 12:43 -0400, Jan Wieck wrote:
>>>     
>>>       
>>>> On 7/11/2006 12:43 PM, Christopher Browne wrote:
>>>>       
>>>>         
>>>>> I have set up tarballs for a first release candidate; Jan should be
>>>>> uploading those soon, and I'll see about updating the web site when
>>>>> they are uploaded...
>>>>>         
>>>>>           
>>>> I have run them through a compile and a few tests and uploaded them to
>>>> the usual location at
>>>>
>>>> http://developer.postgresql.org/~wieck/slony1/download/
>>>>
>>>> One thing I wanted to discuss is related to a recent discussion on the
>>>> hackers mailing list with respect to possible btree corruption due to
>>>> xxid not being suitable. With a single origin setup, there is not
>>>>       
>>>>         
>>> How about one partial index per node?
>>>
>>> CREATE INDEX node5_xid_idx ON sl_log_1(log_xid) WHERE log_origin = 5;
>>>
>>> This should give the performance benefits and prevent index rollover
>>> problems.
>>>
>>> Since you rotate sl_log_N structures, it should be possible to slip in
>>> the index creation or destruction when you truncate the sl_log_N
>>> structure.
>>>     
>>>       
>> This sounds like a valid solution to me,  Anybody else have any thoughts on 
>> this ?
>>   
>>     
>
> Here's code fragments to create per-node partial indexes, that would
> properly be added immediately after the TRUNCATE requests:
>
> -- Code for sl_log_1
> for v_dummy in select no_id from @NAMESPACE at .sl_node n where not exists
>    (select * from pg_catalog.pg_indexes where schemaname = '@NAMESPACE@'
> and tablename = 'sl_log_1' and indexname = 'PartInd-sl_log_1-node-' ||
> n.no_id) loop
>     idef := ''create index "PartInd-sl_log_1-node-'' || v_dummy || '" on
> @NAMESPACE at .sl_log_1 USING btree(log_xid @NAMESPACE at .xxid_ops) where
> (log_origin = '' || v_dummy || '');'';
>     execute idef;
> end loop;
>
> -- Code for sl_log_2
> for v_dummy in select no_id from @NAMESPACE at .sl_node n where not exists
>    (select * from pg_catalog.pg_indexes where schemaname = '@NAMESPACE@'
> and tablename = 'sl_log_2' and indexname = 'PartInd-sl_log_2-node-' ||
> n.no_id) loop
>     idef := ''create index "PartInd-sl_log_2-node-'' || v_dummy || '" on
> @NAMESPACE at .sl_log_2 USING btree(log_xid @NAMESPACE at .xxid_ops) where
> (log_origin = '' || v_dummy || '');'';
>     execute idef;
> end loop;
>
> A little bit of "self-criticism":
>
> - This arbitrarily uses index names of the form
> "PartInd-sl_log_1_node-[node-id]"; perhaps I need to add the cluster
> name into that, in case someone has 2 clusters that participate in one
> database.
>
> - There is no mechanism for dropping out irrelevant indexes should a
> node go away.  Should there be such a mechanism???
>
> - I'd be inclined to add this in as a function, and also call that
> function at the time that sl_log_1/sl_log_2 are created.  The node table
> might be empty, so this could be useless...
>
> - I add in all nodes.  Should I instead add in "all nodes known to be
> origins"?
>   
Per conversation with Jan...

- Preferable to only keep the nodes "known to be origins".  So do both
add and drop of indexes...

- Make this into a stored proc that adds/drops indexes, to the unused
table IFF it knows that table is empty.

- Invoke it at the following times:
  - immediately after TRUNCATE
  - After MOVE SET
  - After CREATE SET
  - After DROP SET
  - After FAILOVER

By running it at those times, that brings things as quickly as possible
to having all (and just) the indexes we want on all the tables they
should be on.