Re: Possible savepoint bug

Michael Paesold <mpaesold(at)gmx(dot)at> writes:
> I am seeing a similar unique index bug here...
> This is PostgreSQL 8.1.1 on RHEL 3, Intel Xeon (i686).

It looks like the problem is that index entries are being inserted out
of order. I find this pair that should be in the other order:

Item 124 -- Length: 44 Offset: 2360 (0x0938) Flags: USED
Block Id: 3 linp Index: 55 Size: 44
Has Nulls: 0 Has Varwidths: 16384

0938: 00000300 37002c40 06000000 64650000 ....7.,@....de..
0948: 1a000000 656e756d 2e626f6f 6b696e67 ....enum.booking
0958: 2e747970 652e6b65 79730000 .type.keys..

Item 125 -- Length: 40 Offset: 2320 (0x0910) Flags: USED
Block Id: 3 linp Index: 48 Size: 40
Has Nulls: 0 Has Varwidths: 16384

0910: 00000300 30002840 06000000 64650000 ....0.(@....de..
0920: 17000000 656e756d 2e626f6f 6b696e67 ....enum.booking
0930: 2e747970 652e4c00 .type.L.

and likewise here:

Item 60 -- Length: 52 Offset: 5060 (0x13c4) Flags: USED
Block Id: 4 linp Index: 38 Size: 52
Has Nulls: 0 Has Varwidths: 16384

13c4: 00000400 26003440 06000000 64650000 ....&(dot)4(at)(dot)(dot)(dot)(dot)de(dot)(dot)
13d4: 24000000 656e756d 2e736572 7665725f $...enum.server_
13e4: 7461736b 5f6c6f67 2e737461 7475732e task_log.status.
13f4: 6b657973 keys

Item 61 -- Length: 56 Offset: 5004 (0x138c) Flags: USED
Block Id: 4 linp Index: 37 Size: 56
Has Nulls: 0 Has Varwidths: 16384

138c: 00000400 25003840 06000000 64650000 (dot)(dot)(dot)(dot)%(dot)8(at)(dot)(dot)(dot)(dot)de(dot)(dot)
139c: 27000000 656e756d 2e736572 7665725f '...enum.server_
13ac: 7461736b 5f6c6f67 2e737461 7475732e task_log.status.
13bc: 52554e4e 494e4700 RUNNING.

All four of the referenced tuples were inserted by XMIN 986, CMIN 0,
which I assume was probably a COPY command. So the breakage occurred
long before the update operations.

Did you create the index before or after loading the initial data?
If you have the original dump that was loaded, it'd be interesting
to see if re-loading it reproduces the corrupt index.

regards, tom lane