I trawled through the first, larger dump you sent me, and found multiple
index entries pointing to quite a few heap tuples:

Occurrences block item

2 43961 1
2 43961 2
2 43961 3
2 43961 4
2 43961 5
2 43961 6
2 43961 7
2 43961 8
2 43961 9
2 119695 1
2 119695 2
2 119695 3
2 126029 1
2 126029 2
2 126029 3
2 166694 1
2 166865 1
2 166865 2
2 166865 3
2 166865 4
2 166865 5
2 166865 6
2 166865 7
2 206221 1
2 247123 1
2 327775 1
2 327775 2
2 327775 3
2 327775 4
2 327775 5
2 327775 6
2 327775 7
2 327775 8
2 327775 9
2 327775 10
2 327775 11

Both indexes show identical sets of duplicates, which makes it pretty
hard to credit that it's a within-index problem.

You mentioned that the test had been allowed to run for a good while
after the first slave error was noted. So it seems there's no question
that we are looking at some mechanism that allows the first few entries
on a heap page to be lost and overwritten :-(, and that this happened
several times over the course of the larger run.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 6/30/2006 9:55 AM, Tom Lane wrote:
> "Marko Kreen" <markokr@gmail.com> writes:
>> The sl_log_* tables are indexed on xid, where the relations between
>> values are not exactly stable. When having high enough activity on
>> one node or having nodes with XIDs on different enough positions
>> funny things happen.
>
> Yeah, that was one of the first things I thought about, but the range of
> XIDs involved in these test cases isn't large enough to involve a
> wraparound, and anyway it's now looking like the problem is loss of heap
> entries, not index corruption at all.
>
> Slony's use of XID comparison semantics for indexes is definitely pretty
> scary though. If I were them I'd find a way to get rid of it. In
> theory, since the table is only supposed to contain "recent" XIDs,
> as long as you keep it vacuumed the index should never contain any
> inconsistently-comparable XIDs ... but in a big index, the boundary keys
> for upper-level index pages might hang around an awful long time ...

With the final implementation of log switching, the problem of xxid
wraparound will be avoided entirely. Every now and then slony will
switch from one to another log table and when the old one is drained and
logically empty, it is truncated, which should reinitialize all indexes.


Jan

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 6/30/06, Jan Wieck <JanWieck@yahoo.com> wrote:
> With the final implementation of log switching, the problem of xxid
> wraparound will be avoided entirely. Every now and then slony will
> switch from one to another log table and when the old one is drained and
> logically empty, it is truncated, which should reinitialize all indexes.

If the xxid-s come from different DB-s, then there can still be problems.

--
marko

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On 6/30/2006 11:17 AM, Marko Kreen wrote:

> On 6/30/06, Jan Wieck <JanWieck@yahoo.com> wrote:
>> With the final implementation of log switching, the problem of xxid
>> wraparound will be avoided entirely. Every now and then slony will
>> switch from one to another log table and when the old one is drained and
>> logically empty, it is truncated, which should reinitialize all indexes.
>
> 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.


Jan

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Jan Wieck <JanWieck@Yahoo.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@.sl_log_1 (
log_origin int4,
log_xid @NAMESPACE@.xxid,
log_tableid int4,
log_actionseq int8,
log_cmdtype char,
log_cmddata text
);
create index sl_log_1_idx1 on @NAMESPACE@.sl_log_1
(log_origin, log_xid @NAMESPACE@.xxid_ops, log_actionseq);

create index sl_log_1_idx2 on @NAMESPACE@.sl_log_1
(log_xid @NAMESPACE@.xxid_ops);

sl_log_1_idx2 doesn't seem to have any such protection. When I was
poking at Marc's example, though, it seemed that the numbers going
into the table were all *locally generated* XIDs, in fact the same
as the XID doing the insertions. If this is only true on the master,
and slaves can be inserting XIDs coming from different masters,
then I think it will break.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
> Marc Munro <marc@bloodnok.com> writes:
>> I'll get back to you with kernel build information tomorrow. We'll also
>> try to talk to some kernel hackers about this.
>
> Some googling turned up recent discussions about race conditions in
> Linux NFS code:
>
> http://threebit.net/mail-archive/lin.../msg05313.html
> http://lkml.org/lkml/2006/3/1/381
>
> I don't know the kernel nearly well enough to guess if these are related
> ...

It may or may not be the same issue, but for what it's worth, we've seen
the same sl_log_1 corruption on AIX 5.1 and 5.3

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

> Jan Wieck <JanWieck@Yahoo.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@.sl_log_1 (
> log_origin int4,
> log_xid @NAMESPACE@.xxid,
> log_tableid int4,
> log_actionseq int8,
> log_cmdtype char,
> log_cmddata text
> );
> create index sl_log_1_idx1 on @NAMESPACE@.sl_log_1
> (log_origin, log_xid @NAMESPACE@.xxid_ops, log_actionseq);
>
> create index sl_log_1_idx2 on @NAMESPACE@.sl_log_1
> (log_xid @NAMESPACE@.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@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Brad Nicholson wrote:
> Tom Lane wrote:
>> Marc Munro <marc@bloodnok.com> writes:
>>> I'll get back to you with kernel build information tomorrow. We'll also
>>> try to talk to some kernel hackers about this.
>> Some googling turned up recent discussions about race conditions in
>> Linux NFS code:
>>
>> http://threebit.net/mail-archive/lin.../msg05313.html
>> http://lkml.org/lkml/2006/3/1/381
>>
>> I don't know the kernel nearly well enough to guess if these are related
>> ...
>
> It may or may not be the same issue, but for what it's worth, we've seen
> the same sl_log_1 corruption on AIX 5.1 and 5.3
>

On 7.4.12, I should add.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Brad Nicholson <bnichols@ca.afilias.info> writes:
> It may or may not be the same issue, but for what it's worth, we've seen
> the same sl_log_1 corruption on AIX 5.1 and 5.3

Hm, on what filesystem, and what PG version(s)?

I'm not completely satisfied by the its-a-kernel-bug theory, because if
it were then ISTM extending an index would be subject to the same risks
as extending a table; but I see no evidence of index page lossage in
Marc's dump. OTOH the usage patterns are different, so maybe PG isn't
stressing the write-to-lseek path quite as hard for indexes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
> Brad Nicholson <bnichols@ca.afilias.info> writes:
>> It may or may not be the same issue, but for what it's worth, we've seen
>> the same sl_log_1 corruption on AIX 5.1 and 5.3
>
> Hm, on what filesystem, and what PG version(s)?
>
> I'm not completely satisfied by the its-a-kernel-bug theory, because if
> it were then ISTM extending an index would be subject to the same risks
> as extending a table; but I see no evidence of index page lossage in
> Marc's dump. OTOH the usage patterns are different, so maybe PG isn't
> stressing the write-to-lseek path quite as hard for indexes.
>
> regards, tom lane

jfs2 in all cases. I don't recall the PG version for 5.1, but it was
greater that 7.4.8. For 5.3, it was 7.4.12.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match