db_change_field() fails to convert int to varchar on PostgreSQL

The root cause of your problem is probably that you incorrectly updated a database from alpha to beta1, and as a consequence the beta1 to beta2 upgrade path is not run correctly. Those updates should not run at all if you are really on beta1.

But. We broke some exception messages that we need to fix. Attached patch restores the proper message placeholder. For reference: we broke that in #642160: Make debug() message work better (usable). Bumping to critical because it makes the whole upgrade path undebuggable.

Refocusing this issue.

i have installed beta1 fresh, then tried to run the updates for beta2 this morning.

i had the same errors, applied damien's patch and got this error 4 times.

Failed: PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying >= integer HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: ALTER TABLE {block_custom} ALTER "format" TYPE varchar USING "format"::varchar; Array ( ) in db_change_field() (line 2895 of /var/www/drupal-7.0-beta2/includes/database/database.inc).

here is the complete report:

The following updates returned messages
block module
Update #7007

    * Failed: PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying >= integer HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: ALTER TABLE {block_custom} ALTER "format" TYPE varchar USING "format"::varchar; Array ( ) in db_change_field() (line 2895 of /var/www/drupal-7.0-beta2/includes/database/database.inc).

text module
Update #7000

    * Failed: PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying >= integer HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: ALTER TABLE {field_data_comment_body} ALTER "comment_body_format" TYPE varchar USING "comment_body_format"::varchar; Array ( ) in db_change_field() (line 2895 of /var/www/drupal-7.0-beta2/includes/database/database.inc).

user module
Update #7015

    * Failed: PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying >= integer HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: ALTER TABLE {users} ALTER "signature_format" TYPE varchar USING "signature_format"::varchar; Array ( ) in db_change_field() (line 2895 of /var/www/drupal-7.0-beta2/includes/database/database.inc).

taxonomy module
Update #7009

    * Failed: PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying >= integer HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: ALTER TABLE {taxonomy_term_data} ALTER "format" TYPE varchar USING "format"::varchar; Array ( ) in db_change_field() (line 2895 of /var/www/drupal-7.0-beta2/includes/database/database.inc).

i am using postgres 8.4.4

looks like "format" is of type "bigint" in my postgres db.

\d block_custom
                                 Table "public.block_custom"
 Column |          Type          |                         Modifiers                          
--------+------------------------+------------------------------------------------------------
 bid    | integer                | not null default nextval('block_custom_bid_seq'::regclass)
 body   | text                   | 
 info   | character varying(128) | not null default ''::character varying
 format | bigint                 | 
Indexes:
    "block_custom_pkey" PRIMARY KEY, btree (bid)
    "block_custom_info_key" UNIQUE, btree (info)
Check constraints:
    "block_custom_bid_check" CHECK (bid >= 0)
    "block_custom_format_check" CHECK (format >= 0)

i suspect perhaps postgres doesn't like the field change.

I spined-off the update exception message patch in #951486: Invalid placeholders in exception messages during update.

Let's refocus this one on the exception on int -> varchar conversion on PostgreSQL.

I experienced the same issue, upgrading from beta1 to beta2 on postgres.

I have the beta2 code running now nevertheless, and it seams to be ok.

However I would like to know whether this leaves me with a problem in my database. Should I revert to beta1? Or can I continue using beta2 even though the database was not properly updated and will I be able to apply future updates?

The reason for the failure lies in the following line (for the block module update):
Check constraints:
"block_custom_format_check" CHECK (format >= 0)

In which a comparison varchar >= int takes place after the conversion. The solution is to remove the check when changing from an unsigned int to something non-int, or when staying at int, but unsigned => TRUE is missing in $spec.

This patch removes/adds the necessary check constraints on db_change_field

Summary of the patch:
- Add a function queryFieldInformation($table, $field) to DatabaseSchema_pgsql which retrieves information about the existing checks for a field
- Execute processField on the $spec in changeField() instead of duplicating code, also handles setting the correct datatype for unsigned int
- Add support for lenght/precision in changeField()

I still observe this on Postgres after updating to 7.0rc1 today.

That's because it's not fixed in RC1 :)

Putting back to 7.x-dev and tagging.

Is this error message related to the issue? It looks to me like string and integer fields are messed up in some places, so I did not want to open a new issue. I am running Drupal 7.0-rc1 with Postgres as database backend.

PDOException: SQLSTATE[22P02]: Invalid text representation: 7 FEHLER: ungültige Eingabesyntax für ganze Zahl: »limited_html«: INSERT INTO myDatabaseName_taxonomy_term_data (vid, name, description, format, weight) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array ( ) in drupal_write_record() (line 6748 of /var/www/drupal/drupal-7.0-rc1/includes/common.inc).

I got this when adding terms to a vocabulary, but I get similar messages whenever adding blocks / terms and such related to the content filter field.

Cheers, Lars.

@grobe: Did you run the latest database updates? If so, it could be related, yes.

@Stevel: I ran the update.php script but got the well-known errors for several modules that were reported for rc1 on postgres. Affected modules are taxonomy, block, user and text. Should I get a dev-snapshot to rerun update.php?

@grobe: Please try -dev or cvs HEAD with the patches from #30 in #710858: Meta issue: fix the remaining PostgreSQL bugs applied to it, and try the upgrade again.

I assume that today's head already has the patch applied? Running update.php now results in

PDOException: SQLSTATE[25P02]: In failed sql transaction: 7 FEHLER: aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert: SELECT 1 AS expression FROM {variable} variable WHERE ( (name = :db_condition_placeholder_0) ) FOR UPDATE; Array ( [:db_condition_placeholder_0] => javascript_parsed ) in variable_set() (line 807 of /var/www/drupal/drupal-7.x-dev/includes/bootstrap.inc).

Edit: I should mention that I have a 8.3 installation of Postgres here.

HEAD only has the patches marked as fixed (green / strike through) applied. Apply the other patches manually and then try to perform the upgrade again (from a copy of the Drupal 6 database).

Hi Stevel, thank you for pointing me at that, I obviously misunderstood the status of the patch. Unfortunately I do not have a Drupal 6 database. I started the affected website (from scratch) once beta1 was out, as that was the first release to have support in the upgrade chain for coming 7.x releases. Cheers, Lars.

edit:

OK, I still applied the patches, and update.php completed. There was a notice that update for the four modules mentioned before resulted in messages, still these were void. I could not observe anything suspicious in my postgres logs neither. Looks like the problem is fixed.

Patches applied:

• postgresql-changefield-null_0.patch
• 951116-unsigned-int-conversion.patch
• 926636-with-database-name.patch

+    // Generate a key to reference this table's information on.
+    $key = $this->connection->prefixTables('{' . $table . '}');
+    if (!strpos($key, '.')) {
+      $key = 'public.' . $key;
+    }
+
+    // Split the key into schema and table for querying.
+    list($schema, $table_name) = explode('.', $key);

This is exactly what DatabaseSchema::getPrefixInfo() is about.

Other then that, this patch looks good to me (I haven't tried to understand the introspection query). Nice clean up of the alter field.

Changed the patch to use getPrefixInfo.

Ok so, to make it short: this patch implements the missing bits of changeField() that are needed for PostgreSQL: make it on par with addField() in regard to how we process unsigned fields and remove the constraints before recreating them.

Good to go.

+++ includes/database/pgsql/schema.inc	14 Dec 2010 15:53:55 -0000
@@ -75,6 +75,40 @@ class DatabaseSchema_pgsql extends Datab
+   * @return
+   *   An object with one member variable:
+   *     - 'checks' that lists all the checks for the field.
+   */
+  public function queryFieldInformation($table, $field) {

Returning an object with one member variable is bit weird. Why don't we return the checks directly? That looks more elegant to me.

Returning an object makes it consistent with queryTableInformation, and it makes it possible to add more data should it be required later.

Anything for Dries.

In my opinion this is critical.

Such opinion requires a strong argument in the form of an issue summary.

The ability to update is an essential feature of Drupal. This bug prevents updates and is therefore critical.

...not in postgres.

However, I'm happy to commit this when it's RTBC.

Damien said in IRC just two three days ago that he considers PostgreSQL unsupported in Drupal 6 and update at your own risk. On the other hand, I filed one of the upgrade path issues as a GCI task -- does this solve all of them failing? Care to maybe run all upgrade tasks in a terminal and attach a screenshot so we can see it passes w/o installing pgsql?

Patch (GCI Task)

(Tested on Ubuntu 10.10: PHP 5.3.3-1ubuntu9.1 PostgreSQL 8.4.5-0ubuntu10.10)

Could you elaborate a bit on your solution? This could do with some comments, because I don't quite understand what's happening here.

Also, minor nit-pick:

if(!in_array(
  if($this

There should be a space between if and ( according to the coding standards.

Update patch

This doesn't account for fields changing from e.g. unsigned int to signed int, or from anything signed / not numeric to an unsigned type.

It also assumes that check conditions follow a specified naming convention, but there is no name explicitly given to the check on creation. Furthermore, when a field is renamed, the name of the check constraint does not change.

Let's get back to #25 ?

I'm recreating patch now testing with schema api and upgrade path test.

As per #30 and #36, here are the test results for the upgrade path testing of #25 (postgresql 9.0.2 / PHP 5.3.3). All tests passing:

Test run started: Sunday, December 26, 2010 - 14:59

Test summary:
-------------

Comment upgrade path 32 passes, 0 fails, 0 exceptions, and 13 debug messages
Basic upgrade path 78 passes, 0 fails, 0 exceptions, and 26 debug messages
Filter format upgrade path 38 passes, 0 fails, 0 exceptions, and 14 debug messages
Node body upgrade path 43 passes, 0 fails, 0 exceptions, and 17 debug messages
Poll upgrade path 218 passes, 0 fails, 0 exceptions, and 37 debug messages
Locale upgrade path 167 passes, 0 fails, 0 exceptions, and 60 debug messages
Taxonomy upgrade path 1265 passes, 0 fails, 0 exceptions, and 50 debug messages
Upload upgrade path 37 passes, 0 fails, 0 exceptions, and 11 debug messages

Test run duration: 3 min 47 sec

I don't know that my patch will help or not but anyway
- pass all upgrade test
- pass schema api test (need to use with http://drupal.org/node/1007448)

Your patch may help if you will explain why it is an improvement.

Let's get this in then. The patch in #20 was RTBC before, and #25 adresses Dries' comment.

So this is RTBC for #25.

chalet16's fix is a lot less invasive, so I'd love Stevel or Damien to take a look at that and see if it makes sense.

For now though, since I need to roll a new RC and I'd like that to have a working PostgreSQL upgrade path, committed #25 to HEAD, with a minor comment fix.