wiki link displayed as missing when version 1 is deleted

[Matt Shin]

Since upgrading to 0.10, it appears that if we link to a wiki page with no version 1, the link will be displayed with a ? at the end, and the wiki page will no longer be listed in the TitleIndex. (Hence, it appears to be missing, when it is actually still there.)

[anonymous]

It looks like this "bug" was introduced by the "optimization" at [3875].

[simon-code@…]

Well, at least it appears in 'Index by Date' :-)

As long as any version can be deleted - including version 1 - it is not really safe to presume a certain version number exists.

[simon-code@…]

Ah. Just noticed #1114, and that the functionality to remove any revision is no longer available. So for recently created projects, the revision=1 will hold true.

That means I have to figure out a way to 'clean up' my old revision list in various projects that are much older than 0.10, as many of them have a number of wiki pages with 'holes' in them - including missing revision 1. I need to basically 'compact' the revision numbers for all existing pages, so that they all start with 1 and move up to the number of revisions in a continuous range.

Ideally such changes as in #1114 should include cleanup as part of trac-admin upgrade, and be executed for installations when such a feature is introduced.

Someone want to help out making the necessary SQL to fix this?

[Christian Boos]

Well, I'd rather fix r3875 than adding overly complex code to compact version numbers (as this could theoretically happen anytime, etc.)

[Matthew Good]

Replying to cboos:

Well, I'd rather fix r3875 than adding overly complex code to compact version numbers (as this could theoretically happen anytime, etc.)

Yeah, compacting version numbers would also impact links to specific version of pages, so I don't think that's an option. I think for 0.10.2 we'll just need to revert r3875, unless the performance has been a serious problem. Then we could try adding an index on "name" for 0.11 if that improves the performance.

Jonas, any suggestions?

[simon-code@…]

I have no strong feelings about the final solution as long as it will work for all projects. I see the point of optimising the index creation, and maybe a modified solution might be to make sure that the lowest version is 1 and leave any other gaps as they are?

I am fixing my environments now regardless, and have this SQL to identify "offenders":

select name, min(version)
from wiki
group by name
having min(version) > 1
order by name;

Then I update records by setting version=1 for each record in the result.

[Dave Matthews]

Is this likely to be fixed? It's easy enough for us to patch 0.10.3 to reverse [3875] for the moment. However, if it's not going to be fixed then we'll have to work out how to update our SQLite databases.

Thanks.

[Christian Boos]

mgood agreed to backout r3875, so I think this is going to be fixed, it's only about who will do it ;)

[Matthew Good]

Ok, the changes from r3875 have been reverted in r4430 and r4431:4432.

[Jonas Borgström]

This is unfortunate since this optimization reduced the query time from about 80ms to < 1ms on t.e.o IIRC. But I guess I'll have to figure out a better way to do this…

[Christian Boos]

Maybe by adding some indexes on the wiki table? Currently, there's only one index on the time column.

[Christian Boos]

oops, too fast, there's the key ('name', 'version') …

So I wonder if having a dedicated index on 'name' would help.

[Jonas Borgström]

Since a "SELECT DISTINCT" query needs to scan through all rows an index will not help. The only thing we can do is to find a way to limit the total number of rows to scan.

[Christian Boos]

Ok, but anyway I wouldn't worry too much about this issue, as the current way of caching the list of all wiki pages is anyway a "hack", and a (IMO) correct approach to this problem has been described in TracDev/Proposals/Journaling.

[zach@…]

On postgres "SELECT name FROM wiki GROUP BY name" is 3 times as fast as the select distinct query on my system.

This is pretty significant, as the query was taking ~400ms before I changed it. This may just be a postgres quirk though, and not apply to sqlite.