The check to see if a constraint exists in postgres which is used before primary keys are created doesn't work if another schema with the same constraint exist. The reason is that pg_constraint which is used to check if the constraint exits stores info for all schemas but uses the connamespace column to manage which schema each constraint belongs to.

I created a patch which will make sure that only constraints from the public schema is checked. This is not a perfect solution, but it should be enough as Drupal doesn't support using multiple schemas yet.

CommentFileSizeAuthor
constraintExists.patch1.28 KBgoogletorp

Comments

damien tournoud’s picture

damien tournoud commented
Status: Needs review » Needs work

This should use the multi-schema properly, ie. use prefixTable() to break down schema and table.

Related: #1008128: Do not use a single underscore as table and index separator on PostgreSQL and SQLite (which fixes that part of the code too)

googletorp’s picture

googletorp commented
Status: Needs work » Needs review

I think you might have mistaken the problem.

Changing how prefixes are created won't help resolve this issue. If you have a public schema with a drupal database and duplicate it to a different schema, you will have identical constraints regardless of the naming convention.

Schemas are like having several databases in the same database. The problem is that all the indexes/ pkeys ect are stored in the same place for all the schemas which leads to this problem.

I don't think that the schema name should be used in the constraint name anyways, which isn't exactly like prefixes anyways. The problem is that the schema name could be changed at some point, and Drupal can't track this. Instead we should rely on Postgres to handle this for us, which is does perfectly well. This information is stored in the pg_namespace table, which is what the patch implements.

damien tournoud’s picture

damien tournoud commented
Status: Needs review » Closed (duplicate)

See the other issue I linked to:

   protected function constraintExists($table, $name) {
-    $constraint_name = '{' . $table . '}_' . $name;
-    return (bool) $this->connection->query("SELECT 1 FROM pg_constraint WHERE conname = '$constraint_name'")->fetchField();
+    $info = $this->getTableObjectName($table, $name);
+    return (bool) $this->connection->query('SELECT 1 FROM pg_constraint WHERE schemaname = :schema AND conname = :name', array(':schema_name' => $info['schema'], ':name' => $info['name']))->fetchField();
   }