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"?