[PATCH] Upgrade.php breaks during upgrade with postgresql 7.4

[lists@…]

I've been attempting to upgrade Maia from RC6 -> 1.0.1 (or rather the latest version from svn). I'm running Maia on Debian Sid (unstable) with postgresql 7.4, I don't know whether these issues are present with pgsql 8.x, if they are not maybe this should be stated somewhere in the documentation.

So far I've found two problems with the upgrade process:

1. ALTER TABLE .. DEFAULT in 4.php

Unfortunately, postgresql doesn't like being told the DEFAULT of a column when it is being added. A separate ALTER TABLE SET DEFAULT statement is required. Since the DEFAULT is set in the next line, I don't see why the DEFAULT '0' is needed anyway.

This patch fixes the issue:

*** 4.php       2006-02-05 19:42:09.266129701 +0000
--- 4.php.old   2006-02-05 19:44:32.385618902 +0000
***************
*** 120,126 ****
                       "CREATE INDEX expires ON maia_tokens ( expires )",
                       
                       "ALTER TABLE maia_mail_recipients ADD COLUMN token char(32)",
!                      "ALTER TABLE maia_users ADD COLUMN quarantine_digest_interval INTEGER",
                       "ALTER TABLE maia_users ALTER quarantine_digest_interval SET DEFAULT '1'",
                       "UPDATE maia_users SET quarantine_digest_interval = '1'",
                       "ALTER TABLE maia_users ALTER quarantine_digest_interval SET NOT NULL",
--- 120,126 ----
                       "CREATE INDEX expires ON maia_tokens ( expires )",
                       
                       "ALTER TABLE maia_mail_recipients ADD COLUMN token char(32)",
!                      "ALTER TABLE maia_users ADD COLUMN quarantine_digest_interval INTEGER DEFAULT '0' NOT NULL",
                       "ALTER TABLE maia_users ALTER quarantine_digest_interval SET DEFAULT '1'",
                       "UPDATE maia_users SET quarantine_digest_interval = '1'",
                       "ALTER TABLE maia_users ALTER quarantine_digest_interval SET NOT NULL",

2. ALTER COLUMN token TYPE in 7.php

maia=> ALTER TABLE maia_mail_recipients ALTER COLUMN token TYPE char(64); ERROR: syntax error at or near "TYPE" at character 53

As far as I know the column type can't be changed once the column has been created:

ALTER TABLE -- Modifies table and column attributes. Parameters type

The data type of a new column being created. (This is used only during the creation of a new column.)

It appears the only way to 'change' the type is to create a new table.

If you need any further info, please let me know.

Thanks,

-jamie

[dmorton]

Can't change a column type after it is created? That's dumb. Well, somehow it has to change.

[dmorton]

According to #postgresql, the route that has to be taken is:

Add new column, copy data into new column, drop old column. Do it all in a transaction.

Ok, and the next suggestion from #postgresql is to upgrade.

I'm tempted to drop all support for postgres.

[dmorton]

Oh, and this is because of limitations in postgres < 8.0 I think we should only support > 8.0

[lists@…]

Just to confirm, I've upgraded to postgres 8.0 and the upgrade process works fine.

[rjl]

Okay, let's change the minimum version of PostgreSQL to 8.0. Once configtest.php and configtest.pl have been updated to test for this, we can close this ticket, as the documentation has already been updated.

[dmorton]

Finding the database version is non-trivial with PEAR::DB, and we'd have to parse the DSN to do it with straight php. Bumping to 1.0.2 milestone.

[dmorton]

[1107] and [1119] Updated configtest.php to check database version,

recommend > 8.0