Re: Index corruption

[Jan Wieck <JanWieck(at)Yahoo(dot)com>]

On 6/30/2006 11:55 AM, Tom Lane wrote:

> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> > On 6/30/2006 11:17 AM, Marko Kreen wrote:
> > > If the xxid-s come from different DB-s, then there can still be problems.
>
> > How so? They are allways part of a multi-key index having the 
> > originating node ID first.
>
> Really?
>
> create table @NAMESPACE(at)(dot)sl_log_1 (
> 	log_origin			int4,
> 	log_xid				@NAMESPACE(at)(dot)xxid,
> 	log_tableid			int4,
> 	log_actionseq		int8,
> 	log_cmdtype			char,
> 	log_cmddata			text
> );
> create index sl_log_1_idx1 on @NAMESPACE(at)(dot)sl_log_1
> 	(log_origin, log_xid @NAMESPACE(at)(dot)xxid_ops, log_actionseq);
>
> create index sl_log_1_idx2 on @NAMESPACE(at)(dot)sl_log_1
> 	(log_xid @NAMESPACE(at)(dot)xxid_ops);

You're right ... forgot about that one. And yes, there can be 
transactions originating from multiple origins (masters) in the same 
log. The thing is, the index is only there because in a single origin 
situation (most installations are), the log_origin is allways the same. 
The optimizer therefore sometimes didn't think using an index at all 
would be good.

However, transactions from different origins are NEVER selected together 
and it wouldn't make sense to compare their xid's anyway. So the index 
might return index tuples for rows from another origin, but the 
following qualifications against the log_origin in the heap tuple will 
filter them out.


Jan

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