I have encountered some bugs which appear to be postgres-specific when upgrading to the latest version of Questionnaire.

I was attempting to upgrade to the latest version of Questionnaire downloaded from the MOODLE_19_STABLE branch on cvs.moodle.org. Our previous version of this plugin would have been based on approximately version 1.20.2.1

There appear to be two issues: please see attachment for more complete error messages.

Warning: pg_query() [function.pg-query]: Query failed: ERROR: value too long for type character varying(20) in /var/www/html/gcm77/moodle23/lib/adodb/drivers/adodb-postgres7.inc.php on line 115
-1: ERROR: value too long for type character varying(20)

This appears to be due to conversion of the field "changed" in the questionnaire_survey table from a timestamp into an integer via the intermediate format of a character(20) field - however, the default value of this field in postgres was now(), which results in a high-precision timestamp such as '2008-01-09 15:22:24.728441' which will not fit into a 20-char field.

Therefore, I propose changing this to a character(30) to allow a bit of leeway.

The second one, "Query failed: ERROR: check constraint "mdl23_quesques_req_ck" is violated by some row in
/var/www/html/gcm77/moodle23/lib/adodb/drivers/adodb-postgres7.inc.php on line 115"

This appears to be due to the part of the upgrade script that attempts to change the enumeration on the field "required" on <prefix>_questionnaire_question to lowercase 'y'/'n'. It was originally upper case 'Y'/'N'. It does not appear actually to convert the value, only attempt to apply the new constraint. It also does not appear to delete the old constraint.

I'm told that a likely reason is that MySQL is case-insensitive with regard to enums, although I don't have enough MySQL experience to verify this, but it would be good if the upgrade script could be modified to remove the old constraint, change the existing Y/N values to lower case, and then put the new constraint in place, when dealing with Postgres databases.

One possibility might be to amend the method getModifyEnumSQL or one of the methods that that calls, to perform the extra steps according to database type. I have been advised to speak to Eloy Lafuente about this.

I will keep this Tracker entry posted as to any discussion that I have, and its outcomes.