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 @[EMAIL PROTECTED] 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
@[EMAIL PROTECTED] USING btree(log_xid @[EMAIL PROTECTED]) where
(log_origin = '' || v_dummy || '');'';
execute idef;
end loop;
-- Code for sl_log_2
for v_dummy in select no_id from @[EMAIL PROTECTED] 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
@[EMAIL PROTECTED] USING btree(log_xid @[EMAIL PROTECTED]) 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"?
_______________________________________________
Slony1-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/slony1-general
