You need to specify the default value, or postgres won't add the not null or primary key constraints

http://api.drupal.org/api/function/db_add_column/6

Note: when you add a column with NOT NULL and you are not sure if there are already rows in the table, you MUST also add DEFAULT. Otherwise PostgreSQL won't work when the table is not empty, and db_add_column() will fail. To have an empty string as the default, you must use: 'default' => "''" in the $attributes array. If NOT NULL and DEFAULT are set the PostgreSQL version will set values of the added column in old rows to the DEFAULT value.

Comments

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented
Status: Active » Postponed (maintainer needs more info) 
array(
      'description' => t('The primary identifier for the feed.'),
      'type' => 'int',
      'unsigned' => TRUE,
      'not null' => TRUE,
      'default' => 0,
    )

Strange, that default value is ecplicitly told here to be 0.
Can you try it out with 'default' => "0" ?

stormsweeper’s picture

stormsweeper commented

It's fine on install, it's the update hook:

(revision 1.5.2.21.2.13 in DRUPAL-6--1)

/**
 * Adds vid to feedapi table
 */
function feedapi_update_6105() {
  $ret = array();
  db_add_field($ret, 'feedapi', 'vid',
    array(
      'description' => t('The primary identifier for the feed.'),
      'type' => 'int',
      'unsigned' => TRUE,
      'not null' => TRUE
    )
  );
  db_drop_primary_key($ret, 'feedapi');
  db_query("UPDATE feedapi f, node n SET f.vid = n.vid WHERE n.nid = f.nid");
  db_query("UPDATE feedapi SET vid = nid WHERE vid = 0");
  db_add_primary_key($ret, 'feedapi', array('vid'));
  db_add_index($ret, 'feedapi', 'nid', array('nid'));
  return $ret;
}

So for an update from 1.6, db_add_field() fails to add the not null constraint if there are existing rows, as those values will be null, and then the db_add_primary_key() will fail for the same reason.

stormsweeper’s picture

stormsweeper commented
Title: Update 6105 does not work on Postgres as it lacks a default value for vid » Update 6105 does not work on Postgres

I also noticed that the first update query as written will not work on Postgres, as it uses a different format for updates from other tables. The table prefix braces are also missing from those queries.

I'll roll a patch for these changes.

stormsweeper’s picture

stormsweeper commented

Sorry, my wife went into labor the afternoon after I last updated, so I have been unable to roll any patches. :) Multi-table references in update queries are pretty varied across db engines, so for now you would need to branch on $GLOBALS['db_type']. The Postgres format is something like

UPDATE {nodeapi} SET vid = node.vid FROM node;

Postgres versions before 8.2 don't support aliases in UPDATE statements, either. Probably not a huge issue since most people running Postgres will be on 8.2 or 8.3 at this point, but doesn't hurt to conform to 7.4's spec for 5.x-6.x

http://www.postgresql.org/docs/7.4/interactive/sql-update.html

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented
Status: Postponed (maintainer needs more info) » Fixed

feedapi_update_6105() seems to be branched on $GLOBALS['db_type'] now.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.