Issue status update for http://drupal.org/node/29082 Post a follow up: http://drupal.org/project/comments/add/29082 Project: Drupal Version: cvs Component: postgresql database Category: tasks Priority: normal Assigned to: Cvbge Reported by: Cvbge Updated by: Cvbge -Status: patch (code needs work) +Status: patch (code needs review) Attachment: http://drupal.org/files/issues/screenshot_3.jpg (119.33 KB) With attached patch I could make all updates without errors (my LOCKs patch was needed - http://drupal.org/node/22911). There are still some issues that should be addressed (see the patch). Also mysql update is not tested - please test. There are some problems when updating from 4.6 to cvs. Maybe the update should be made by 4.6 base itself? So I've started with fresh 4.6.3. After changing the base to cvs, patching with the LOCKs patch and simple debug helper patch, on update.php?op=update I get following errors: warning: pg_query(): Query failed: ERROR: column "cache" of relation "sessions" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71. Warning: pg_query(): Query failed: ERROR: column "referer" of relation "watchdog" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71 Bad query: Array ( [0] => SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = 'd5b1aa940eaeab37fe690ce9cc500396' AND u.status < 3 LIMIT 1 OFFSET 0 [1] => SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur ON ur.rid = r.rid WHERE ur.uid = 1 [2] => SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) [3] => SELECT * FROM access WHERE status = 0 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) [4] => SELECT data, created, headers, expire FROM cache WHERE cid = 'variables' [5] => SELECT COUNT(pid) FROM url_alias [6] => SELECT name, filename, throttle, bootstrap FROM system WHERE type = 'module' AND status = 1 [7] => UPDATE sessions SET uid = 1, cache = 0, hostname = '127.0.0.1', session = 'messages|a:0:{}', timestamp = 1124545039 WHERE sid = 'd5b1aa940eaeab37fe690ce9cc500396' [8] => INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query(): Query failed: ERROR: column "cache" of relation "sessions" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '', '/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php', '127.0.0.1', 1124545039) ) Fatal error: ERROR: column "referer" of relation "watchdog" does not exist query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query(): Query failed: ERROR: column "cache" of relation "sessions" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '', '/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php', '127.0.0.1', 1124545039) in /var/www/dt/d/includes/database.pgsql.inc on line 94 Warning: Unknown(): A session is active. You cannot change the session module's ini settings at this time. in Unknown on line 0 As we can see the problem is that there is no cache column is session table - that's update_130. Also there's no column 'referer' in watchdog table - that's update_142. So I've commented out update_130 and 142, added the columns by hand in the db and started again from the begining. After that I got another error: warning: pg_query(): Query failed: ERROR: column "access" of relation "users" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71. Bad query: Array ( [0] => SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = 'd5b1aa940eaeab37fe690ce9cc500396' AND u.status < 3 LIMIT 1 OFFSET 0 [1] => SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur ON ur.rid = r.rid WHERE ur.uid = 1 [2] => SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) [3] => SELECT * FROM access WHERE status = 0 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) [4] => SELECT data, created, headers, expire FROM cache WHERE cid = 'variables' [5] => SELECT COUNT(pid) FROM url_alias [6] => SELECT name, filename, throttle, bootstrap FROM system WHERE type = 'module' AND status = 1 [7] => UPDATE sessions SET uid = 1, cache = 0, hostname = '127.0.0.1', session = 'messages|a:0:{}', timestamp = 1124547147 WHERE sid = 'd5b1aa940eaeab37fe690ce9cc500396' [8] => UPDATE users SET access = 1124547147 WHERE uid = 1 [9] => INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query(): Query failed: ERROR: column "access" of relation "users" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '', '/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php', '127.0.0.1', 1124547147) ) user error: query: UPDATE users SET access = 1124547147 WHERE uid = 1 in /var/www/dt/d/includes/database.pgsql.inc on line 94. That's update_136. So, disable update_136 and do it by hand and start again. After all that, when accessing update.php?op=update I noticed no errors. After pressing Update all updates were applied without any errors. But when accessed main page I got a screen as on attached screenshot. Cvbge Previous comments: ------------------------------------------------------------------------ Wed, 17 Aug 2005 21:25:43 +0000 : Cvbge Postgres, since 6.4, has version() function so you can check db version from SQL. This can be used in updates.inc for example for conditional dropping of the column (which is available since 7.3, not 7.4 as stated in updates.inc). I need to check how the version string is returned in different versions before making changes. ------------------------------------------------------------------------ Thu, 18 Aug 2005 10:08:25 +0000 : Cvbge Attachment: http://drupal.org/files/issues/drupal_pg_version2.diff (1.01 KB) Here's a function returning postgres db version. I'm not sure if it fits in updates.inc, maybe it should be somewhere else. ------------------------------------------------------------------------ Fri, 19 Aug 2005 14:33:32 +0000 : Cvbge Attachment: http://drupal.org/files/issues/drupal_pg_version_and_other.diff (2.76 KB) Added 2 new functions to add or change column definition. The take care about mysql/pgsql. Code not tested, will test and update old code when I hear that this has a chanse to go in ;) ------------------------------------------------------------------------ Fri, 19 Aug 2005 17:03:39 +0000 : Cvbge Attachment: http://drupal.org/files/issues/updates.inc-pgsql_fix.diff (9.97 KB) Still more work needed.