The most recent update_N is mixing types in a join, and so fails.

WD php: pg_query(): Query failed: ERROR:  operator does not exist: integer = character varying                  [error]
LINE 3:       INNER JOIN node n ON n.nid = m.did
                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. in
/home/drupal6/drupal-6.19/includes/database.pgsql.inc on line 139.
WD php:                                                                                                         [error]
query: SELECT n.type, m.did
      FROM mollom m
      INNER JOIN node n ON n.nid = m.did
      WHERE m.entity = 'node' in /home/drupal6/sites/all/modules/mollom/mollom.install on line 555.
ALTER TABLE {mollom} ADD COLUMN form_id varchar(255) NOT NULL default ''                              [success]
UPDATE {mollom} SET form_id = 'user_register' WHERE entity = 'user'                         [success]
UPDATE {mollom} SET form_id = 'contact_mail_page' WHERE entity = 'session'                  [success]
UPDATE {mollom} SET form_id = CONCAT('webform_client_form_', did) WHERE entity = 'webform'  [success]
UPDATE {mollom} SET form_id = 'comment_form' WHERE entity = 'comment'                       [success]
Executing mollom_update_6116                                                                                    [success]
WD php: pg_query(): Query failed: ERROR:  constraint "mollom_pkey" does not exist in                            [error]
/home/drupal6/drupal-6.19/includes/database.pgsql.inc on line 139.
WD php:                                                                                                         [error]
query: ALTER TABLE mollom DROP CONSTRAINT mollom_pkey in /home/drupal6/drupal-6.19/includes/database.pgsql.inc
on line 752.
ALTER TABLE {mollom} DROP CONSTRAINT {mollom}_pkey                                                              [error]
ALTER TABLE {mollom} RENAME "did" TO "did_old"                                              [success]
ALTER TABLE {mollom} ADD COLUMN id varchar(32) default ''                                             [success]
UPDATE {mollom} SET id = CAST(did_old AS varchar)                                                               [success]
ALTER TABLE {mollom} ALTER id SET NOT NULL                                                                      [success]
ALTER TABLE {mollom} DROP COLUMN did_old                                                                        [success]
ALTER TABLE {mollom} ADD PRIMARY KEY (entity,id)                                                                [success]
DROP INDEX {mollom}_session_idx                                                                                 [success]
ALTER TABLE {mollom} RENAME "session" TO "session_old"                                      [success]
ALTER TABLE {mollom} ADD COLUMN session_id varchar(255) default ''                                    [success]
UPDATE {mollom} SET session_id = CAST(session_old AS varchar)                                                   [success]
ALTER TABLE {mollom} ALTER session_id SET NOT NULL                                                              [success]
ALTER TABLE {mollom} DROP COLUMN session_old                                                                    [success]
CREATE INDEX {mollom}_session_id_idx ON {mollom} (session_id)                                                   [success]
Executing mollom_update_6117                                                                                    [success]
ALTER TABLE {mollom_form} ADD COLUMN discard smallint NOT NULL default 1                                        [success]
Executing mollom_update_6118                                                                                    [success]
pg_query(): Query failed: ERROR:  operator does not exist: integer = character varying                          [warning]
LINE 3:       INNER JOIN node n ON n.nid = m.did
                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. in
/home/drupal6/drupal-6.19/includes/database.pgsql.inc on line 139.
                                                                                                                [error]
query: SELECT n.type, m.did
      FROM mollom m
      INNER JOIN node n ON n.nid = m.did
      WHERE m.entity = 'node' in /home/drupal6/sites/all/modules/mollom/mollom.install on line 555.
pg_query(): Query failed: ERROR:  constraint "mollom_pkey" does not exist in                                    [warning]
/home/drupal6/drupal-6.19/includes/database.pgsql.inc on line 139.
                                                                                                                [error]
query: ALTER TABLE mollom DROP CONSTRAINT mollom_pkey in /home/drupal6/drupal-6.19/includes/database.pgsql.inc
on line 752.
An error occurred at function : drush_core_updatedb_batch_process                                               [error]
Finished performing updates.                                                                                    [ok]
An error occurred at function : drush_core_updatedb                                                             [error]

Comments

the end result is, i'm now getting these errors when deleting nodes

warning: pg_query(): Query failed: ERROR: column "id" does not exist LINE 1: DELETE FROM mollom WHERE entity = 'node' AND id IN ('29981') ^ in /home/drupal6/drupal-6.19/includes/database.pgsql.inc on line 139.
user warning: query: DELETE FROM mollom WHERE entity = 'node' AND id IN ('29981') in /home/drupal6/sites/all/modules/mollom/mollom.module on line 494.

Version:6.x-1.9» 6.x-1.15

Hm. That's tough.

See also #951116: db_change_field() fails to convert int to varchar on PostgreSQL for Drupal core -- I can only hope that our Drupal PostgreSQL maintainers will come up with a simple resolution.

@sun that is a select query which is essentially try to join on columns of different types. What Shiny is saying is that you shouldn't be doing that. It makes no sense. m.did should be an integer.

Problem space being that we have:

{mollom}.entity (varchar)
{mollom}.id (varchar)

Depending on .entity, .id may be an integer or a string. Therefore, .id is a varchar column, as that allows to store both.

So the problem arises when we do:

SELECT ... FROM {node} n INNER JOIN {mollom} m ON m.entity = 'node' AND m.id = n.nid

...which apparently happened in the module update for the first time.

MySQL doesn't seem to have a problem with that. If there is no simple conversion or less-strict-mode method for PostgreSQL, then I guess we need to rewrite that module update into a Batch API driven update of separate SELECT/UPDATE queries.

I'd love to hear about other possibilities, as we're planning to perform such queries more often in the future.

it looks like a performance hit also, to make the database join on different types. The index will be on the varchar, not the varchar converted to int.

I'm getting the same error as well. Are there any fixes for this?

For pgsql, it would work to write m.id=cast(n.nid as text) instead of m.id=n.nid
The CAST operator also exists in mysql, but unfortunately it accepts only a small subset of the type names and neither TEXT nor INTEGER are part of them, so it 's not obvious how to make the exact same query to work in both databases.

Another way of looking at the problem is to make pgsql globally accept the integer=text comparison, which can be done by creating an implicit cast:

CREATE FUNCTION int_to_text(INT4) RETURNS TEXT AS 'SELECT textin(int4out($1));' LANGUAGE SQL STRICT IMMUTABLE;
CREATE CAST (INT4 AS TEXT) WITH FUNCTION int_to_text(INT4) AS IMPLICIT;

And then the original query would work unmodified.