Opened 13 years ago

Closed 12 years ago

Last modified 13 years ago

#269 closed defect (fixed)

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

Reported by: lists@… Owned by: rjl
Priority: normal Milestone: 1.0.2
Component: PHP scripts Version: 1.0.0
Severity: normal Keywords:
Cc:

Description

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",
  1. 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

Change History (9)

comment:1 Changed 13 years ago by dmorton

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

comment:2 Changed 13 years ago by 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.

comment:3 Changed 13 years ago by dmorton

  • Severity changed from major to normal

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

comment:4 Changed 13 years ago by lists@…

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

comment:5 Changed 13 years ago by 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.

comment:6 Changed 13 years ago by anonymous

  • Milestone set to 1.0.1
  • Version changed from 1.0.1 Devel to 1.0.0

comment:7 Changed 13 years ago by dmorton

  • Milestone changed from 1.0.1 to 1.0.2

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.

comment:8 Changed 13 years ago by rjl

  • patch set to 1
  • Summary changed from Upgrade.php breaks during upgrade with postgresql 7.4 to [PATCH] Upgrade.php breaks during upgrade with postgresql 7.4

comment:9 Changed 12 years ago by dmorton

  • Resolution set to fixed
  • Status changed from new to closed

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

recommend > 8.0

Note: See TracTickets for help on using tickets.