When loading the database for the first time, from database.pgsql, you issue e.g.:

  title varchar(64) NOT NULL default '',
  UNIQUE (title)

The UNIQUE(title) will create index boxes_title_key.
When using a prefix (not schema type prefix), e.g. foo_, it'll create table foo_boxes and index foo_boxes_title_key.
When using schema prefix, e.g. foo., it'll create table boxes and index boxes_title_key in foo schema.

The problem arises when we want to change database in update.php. For example if we want to drop UNIQUE(title) constraint, we do:
ALTER TABLE {boxes} DROP CONSTRAINT {boxes}_title_key

If the prefix is not schema type, e.g. foo_, this will work. But if the prefix is schema type, e.g. foo., this won't work. It will be translated to
ALTER TABLE foo.boxes DROP CONSTRAINT foo.boxes_title_key which will fail, because there is no contraint foo.boxes_title_key

Currently I see only one solution to this problem: explicitely name constraints in CREATE TABLE so they do not use prefixes, and drop prefixes from update clauses.
This would create problems for people upgrading from 4.6, as their constraints do use prefixes.

#1 drupal-head_1.diff8.5 KBCvbge
Members fund testing for the Drupal project. Drupal Association Learn more


Cvbge’s picture

8.5 KB

For now this... but I've run into another problem... CREATE INDEX schema.indexname ... is a no-no... So far looks like schma prefixes and normal prefixes are not compatibile... I need to think more...

Cvbge’s picture

Status: Active » Closed (won't fix)

So it seems that you can't nicely fix the CREATE index schema/non schema prefix incompatibility...
Marking won't fix for now, maybe something will come up for 4.8