content_db_index_exists performs a query to verify whether an index already exists in the database. syntax of the query is incompatible with how Drupal handles indexes in Postgres causing the function to always return FALSE (even if an index already exists).

Current implementation:

function content_db_index_exists($table, $name) {
  global $db_type;
  if ($db_type == 'mysql' || $db_type == 'mysqli') {
    return (bool)db_result(db_query("SHOW INDEX FROM {". $table ."} WHERE key_name = '$name'"));
  }
  elseif ($db_type == 'pgsql') {
    return (bool)db_result(db_query("SELECT COUNT(indexname) FROM pg_indexes WHERE indexname = '$name'"));	
  }
  return FALSE;
}

suggested fix:

replace the line after elseif ($db_type == 'pgsql') { with 

return (bool)db_result(db_query("SELECT COUNT(indexname) FROM pg_indexes WHERE indexname = $table.'_'.$name.'_idx'"));

which will be consisted with how indexes are named in database.pgsql.inc.

Comments

przadka’s picture

przadka commented

Sorry, there was a typo in the fix I suggested. The correct version:

return (bool)db_result(db_query("SELECT COUNT(indexname) FROM pg_indexes WHERE indexname = '".$table."_".$name."_idx'"));
markus_petrux’s picture

markus_petrux commented
Status: Active » Postponed (maintainer needs more info)

Now, I'm not sure if this would be compatible with all postgresql versions that we would have to offer support to. I coded this like it is in the Drupal related issue that's trying to add this feature to Drupal itself, and I'm monitoring that one to see where it goes. This is it: #360854: db_index_exists() missing, module updates cannot handle indexes properly. I assumed the PostgreSQL related method implemented in that issue was ok. So. I'm not sure now which is the best way to proceeed, at least until we know more about how to do this properly in PostgreSQL and it works for all versions.

For reference, the issue where this was implemented in CCK is this one: #231453: Allow indexing columns.

Maybe you could chime in the Drupal related issue as well if you can provide information and test in PostgrSQL?

markus_petrux’s picture

markus_petrux commented

@przadka: The following post suggests the indexname column stores the user defined name of the index.

http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796

Also the PostgreSQL manuals suggest indexname column stores the user defined name for the index:

http://www.postgresql.org/docs/7.4/static/view-pg-indexes.html
http://www.postgresql.org/docs/8.4/static/view-pg-indexes.html

I'm unable to find information about changes in the way PostgreSQL stores data in the indexname column. That's a reference to pg_class table, and its documentation seems to suggest the value is not an internal name, but the user defined name for tables, indexes, and views.

http://www.postgresql.org/docs/7.4/static/catalog-pg-class.html
http://www.postgresql.org/docs/8.4/static/catalog-pg-class.html

Are you sure our current implementation here is not correct? Could you please double check this at your end? Do we need to take into account differences between PostgreSQL versions? Any hint on where we can find documentation about it?

markus_petrux’s picture

markus_petrux commented
Status: Postponed (maintainer needs more info) » Active

LOL... you'll now see... it was soooo much easy. The issue here is that the Drupal schema API for PostgreSQL builds the name of the indexes like this: $table .'_'. $index_name .'_idx'.

Of course, this needs to be fixed in our implementation of content_db_index_exists(). I'll build a patch as soon as possible.

markus_petrux’s picture

markus_petrux commented
Status: Active » Fixed

Committed a slight variation of the code in #1 (see patch) to CVS (branches CCK2 and CCK3).

Note that the table name needs to be enclosed between {} so that db_prefix can be applied correctly.

Status: Fixed » Closed (fixed)

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