if you try to update a 4.7 pgsql site to 5.0 (after applying http://drupal.org/node/82177 http://drupal.org/node/82524 and http://drupal.org/node/82806 to update.php and friends so the update works at all), you'll find that update 1005 contains bogus SQL that's invalid on pgsql. here are the errors:

warning: pg_query(): Query failed: ERROR: syntax error at or near "unsigned" at character 224 in /Users/wright/drupal/cvs/head/core.cvs/includes/database.pgsql.inc on line 119.
user warning: query: CREATE TABLE node_type ( type varchar(32) NOT NULL, name varchar(255) NOT NULL, module varchar(255) NOT NULL, description text NOT NULL, help text NOT NULL, has_title integer unsigned NOT NULL, title_label varchar(255) NOT NULL default '', has_body integer unsigned NOT NULL, body_label varchar(255) NOT NULL default '', min_word_count integer unsigned NOT NULL, custom smallint NOT NULL DEFAULT '0', modified smallint NOT NULL DEFAULT '0', locked smallint NOT NULL DEFAULT '0', orig_type varchar(255) NOT NULL default '', PRIMARY KEY (type) ); in /Users/wright/drupal/cvs/head/core.cvs/includes/database.pgsql.inc on line 138.
warning: pg_query(): Query failed: ERROR: relation "node_type" does not exist in /Users/wright/drupal/cvs/head/core.cvs/includes/database.pgsql.inc on line 119.
user warning: query: SELECT * FROM node_type WHERE type = 'page' in /Users/wright/drupal/cvs/head/core.cvs/includes/database.pgsql.inc on line 138.
warning: pg_query(): Query failed: ERROR: relation "node_type" does not exist in /Users/wright/drupal/cvs/head/core.cvs/includes/database.pgsql.inc on line 119.
user warning: query: INSERT INTO node_type (type, name, module, has_title, title_label, has_body, body_label, description, help, min_word_count, custom, modified, locked, orig_type) VALUES ('page', 'page', 'node', 1, 'Title', 1, 'Body', 'If you want to add a static page, like a contact page or an about page, use a page.', '', 0, 1, 1, 0, 'page') in /Users/wright/drupal/cvs/head/core.cvs/includes/database.pgsql.inc on line 138.
warning: pg_query(): Query failed: ERROR: relation "node_type" does not exist in /Users/wright/drupal/cvs/head/core.cvs/includes/database.pgsql.inc on line 119.
...

after that, there are a ton of similar errors about "node_type" not existing.

should be pretty easy to fix, i just don't have time right now. if no one else beats me to it, i'll roll a patch later.

CommentFileSizeAuthor
#5 update_1005_pgsql.patch_2.txt1.49 KBdww
#4 pgsql_types_20060907_1719_patch.txt1.41 KBsammys
#2 pgsql_types_20060907_1705_patch.txt1.57 KBsammys
#1 update_1005_pgsql.patch.txt1020 bytesdww

Comments

dww’s picture

dww
we/he/they
commented
Status: Active » Needs review
StatusFileSize
new update_1005_pgsql.patch.txt1020 bytes

alas, this one is harder to fix correctly than 1006 was (http://drupal.org/node/82823). the problem is that pgsql doesn't natively know about "unsigned" at all. attached is the trivial, somewhat lame way to solve this -- just remove the "unsigned" keyword. it works, but isn't ideal if the code is expecting this to remain unsigned, and isn't consistent with other parts of the pgsql schema.

sammys recently added some handy code to define a nice "int_unsigned" type in pgsql for exactly this problem. unfortunately, the code only happens on a new install -- there was no system update added to define these types when migrating from a 4.7 DB to a 5.0 one on pgsql. :( see http://drupal.org/node/76681#comment-133583 for more details.

so, our options are:

  1. apply the somewhat lame patch attached here and forget about unsigned in the {node_type} table on pgsql.
  2. add code to update 1005 to conditionally define the "int_unsigned" types as an upgrade path from 4.7 to 5.0, then convert the "integer unsigned" to "int_unsigned".
  3. appy the lame patch here, then add a new update 1012 that defines the int_unsigned type, converts {node_type}'s 3 fields to use it, and performs any other schema conversions that were done in http://drupal.org/node/76681

i don't have enough of a pgsql itch to really champion this effort. i'm just laying out the options, and providing a minimal solution to the currently broken update.

sammys’s picture

sammys commented
StatusFileSize
new pgsql_types_20060907_1705_patch.txt1.57 KB

Here is the patch to fix that update and to have the new type aliases added during updates.

--
Sammy Spets
Synerger
http://synerger.com

dww’s picture

dww
we/he/they
commented

personally, i think i'd prefer defining those pgsql unsigned types directly in update 1005 and using those directly. as it is now, it makes the code seem a little more ugly and counter-intuitive. if 1005 is the first update that needs the new types, let's just define them and use them.

sammys’s picture

sammys commented
StatusFileSize
new pgsql_types_20060907_1719_patch.txt1.41 KB

here you go

dww’s picture

dww
we/he/they
commented
Status: Needs review » Reviewed & tested by the community
StatusFileSize
new update_1005_pgsql.patch_2.txt1.49 KB

love it. 2 tiny changes: 1) removed a few stray spaces, 2) re-rolled from the root of the source. otherwise, i re-tested this and it works great in pgsql (and, just to be extra paranoid, i reconfirmed that mysql still works). RTBC.

thanks!
-derek

dries’s picture

dries commented
Status: Reviewed & tested by the community » Fixed

Committed to CVS HEAD. Thanks!

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)