Error in db upgrade (OJS 2.4.1 --> OJS 2.4.8) with Postgresql

Software Support
1

Hello,
while I was trying to update an OJS 2.4.1 to 2.4.8 I got this error

[schema: dbscripts/xml/ojs_schema.xml]
[data: dbscripts/xml/indexes.xml]
ERROR: Upgrade failed: DB: ERROR:  index row size 2888 exceeds maximum 2712 for index "article_supp_file_settings_name_value"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

and the upgrade was stopped.

Wich solution can I try?

Thanks,
andrea

2

Hi @marchitelli,

The problematic index creation is implemented here:

I don’t know how/whether PostgreSQL’s optimizer will use this, but the message recommends an MD5 hash, as does this post for a similar issue in another application, so you could do that by changing the line to this:

CREATE INDEX article_supp_file_settings_name_value ON article_supp_file_settings (setting_name(50), md5(setting_value(150)));

I haven’t tested this myself.

Regards,
Alec Smecher
Public Knowledge Project Team

3

Sorry, @asmecher, this solution doesn’t work: the error is the same.

Andrea

4

Hi @marchitelli,

Hmm, could you try wrapping the setting_name in md5(...) as well? OTOH the setting name is pretty short, but maybe that’s necessary too.

Regards,
Alec Smecher
Public Knowledge Project Team

5

Thanks @asmecher for pointing it out; I solved in this way

<query driver="postgres7">
	CREATE INDEX article_supp_file_settings_name_value ON article_supp_file_settings (setting_name, md5(setting_value))
</query>

andrea

6

Hi @marchitelli,

Oops, my typo! Your solution is correct, thanks. Glad to hear it’s working.

Regards,
Alec Smecher
Public Knowledge Project Team