Indexes create overlong identifiers in PostgreSQL

Same issue here.

I'm pretty sure this is a bug in Drupal core, though I don't know whether it isn't already fixed. Could you please test whether this still occurs with the latest version of Drupal core?

@ Core DB guys: It seems an index identifier is automatically created by concatenating table name, index name and a "idx" suffix, without checking the length of the resulting identifier. This leads to above error.

I'm surprised this issue still persists with no decision made one way or another. If the current implementation won't be fixed then a warning should be explicitly given or at least an error in place of kevee's workaround.

I believe it is broken as it stands now and is pretty much useless for the majority of typically long field_revision_field_ table indexes, which would end up causing migration issues, for example, from mySQL to pgSQL using the DBTNG module.

Although table name lengths can be changed above 63 characters it requires re-compilation of the postgresql source code which won't be viable nor straightforward for many people.

Also, since index drops (and keys) in pgsql's schema.inc file look for the $name defined by the aforementioned prefixNonTable function we can't simply let postgreSQL take care of automatically generating truncated index names (often by removing trailing letter between underscores and adding a number to idx (e.g. ... _field_ ... becomes ... _f_ ...).

The other issue is that simply truncating names leads to duplication errors, especially from the end of table index names but still possible from the front as well. Since Drupal anticipates a particular name we also can't rely on callbacks from postgresql to avoid name clashes.

I circumvented this issue to an extent (similarly overriding prefixNonTable within the schema.inc file) by generating a crc32b hash of the final name ($name) determined by the prefixNonTable function and then adding the calculated 8-character hash to the beginning of the last 55 characters in $name.

For example,

	/**
	* Create names for indexes, primary keys and constraints.
	*
	* This prevents using {} around non-table names like indexes and keys.
	*
	* This overrides the default prefix to shorten index names to be only 63 characters long.
	*/
	function prefixNonTable($table) {
		$args = func_get_args();
		$info = $this->getPrefixInfo($table);
		$args[0] = $info['table'];
		$name = implode('_', $args);
		if(strlen($name) > 63) {
			$hashed_name = hash("crc32b", $name); //crc32b hash is always 8 characters
			$name = $hashed_name . substr($name, -55); //$name is truncated to last 55 characters and 8 character hash is prefixed
		}
		return $name;
	}

Another function that needs to be changed reflects what appears to be a bug in Drupal Core anyway. The indexExists function includes curly brackets around the $table name, which was expressly avoided in the first place in the prefixNonTable function upon its creation. I've removed the curly brackets prior to hashing. However, this function is still buggy since it assumes there's no table prefix. I can't work out why prefixNonTable() wasn't used.

//modified to include hash prefix if more than 63 characters and removed curly braces
  public function indexExists($table, $name) {
    // Details http://www.postgresql.org/docs/8.3/interactive/view-pg-indexes.html
    $index_name = $table . '_' . $name . '_idx';
	if(strlen($index_name) > 63) {
			$hashed_index_name = hash("crc32b", $index_name);
			$index_name = $hashed_index_name . substr($index_name, -55);
		}    
    return (bool) $this->connection->query("SELECT 1 FROM pg_indexes WHERE indexname = '$index_name'")->fetchField();
  }

The first problem is that I don't know what impact this name change could have elsewhere. The second problem is that crc32b is only 8 characters long so name clashes are statistically very improbable rather than near impossible. Another issue is that a table called foo_bar with a column horse ends up concatenating the same as a table foo with column bar_horse. My hash doesn't avoid these scenarios so perhaps concatenating string length of table name before hashing could help or simply using double underscores between table and field names.

I'm migrating to pgSQL to take advantage of PostGIS but I'm a little worried now if there would be many more oversights with contributed modules due to its relatively low usage with Drupal.

Cheers

Probably needs greater attention.

@cmonnow we also migrated from D6 and MySQL to D7 and PostgreSQL for the same reason (PostGIS). Drupal itself works without any noticeable problems with PG since D 7.2. As we only use less than a handful of contrib modules we have no problems with that but there are known issues with contrib modules I once stumbled upon while migrating so we wrote own modules as replacement.

cheers bzrudi

Thanks bzrudi. I'm hoping that the few contrib modules I use also end up bug-free. As long as Views works fine and the Panopoly distro I happened to start with doesn't perform any obscure queries I should be OK.

So far I've found nearly every module I've used does everything except for the basic feature I want so I end up writing my own code anyway (I guess because I'm offloading a lot of processing to the client-side and third-party APIs). What's crazy is the number of bugs I've discovered in what I would expect as a primary functionality of a particular module, making me believe nearly all developers are customising their own Drupal modules.

Good progress being made in https://www.drupal.org/node/998898, with patches available for testing in D7 and D8.