Multiple issues when PostgreSQL is used with non-public schema

Thanks for the detailed bug report!

It looks like this is happening when the administration role gets created, via this code:

  // Create a default role for site administrators, with all available permissions assigned.
  $admin_role = new stdClass();
  $admin_role->name = 'administrator';
  $admin_role->weight = 2;
  user_role_save($admin_role);
  user_role_grant_permissions($admin_role->rid, array_keys(module_invoke_all('permission')));

Somehow the role ID is winding up NULL, so the issue must occur in user_role_save(), before it.

If you look in the {watchdog} table in Drupal's database after this occurs, what kind of messages appear at the end? Are there any relevant error messages?

Hm, I am not really sure how to intrepet that. Normally the "variables" column of that database table would contain something human-readable, but in your case it doesn't seem to....

I'm afraid I don't know a whole lot about PostgreSQL myself, but perhaps moving it to a different place in the issue queue will get some other people's eyes on it?

Anybody have a work around? This worked fine in 6...

Ok, this makes sense to me now. I'm happy with the commit. Its kinda minor since its more a PostgreSQL specific issue than anything to do with Drupal.

Er, there is no patch here :) Maybe you posted that in the wrong issue?

LOL, I fully did, apologies!

Same error presented itself when trying to install Drupal in drupal schema.

Environment:
PHP 5.3.6
PostgreSQL 8.4.6
Apache 2.2.11
Solaris 5.11 snv_130

Would be nice for this to be addressed as it breaks Drupal installation and forces installation into a public schema, which depending on the use case of Drupal may not be ideal. Thanks.

Most likely, the ID generator doesn't play nice with schemas.

From looking at the code, it seems that we are reusing the sequence generator from the {sequences}.value serial column. This is an horrible hack, and I bet that the sequence doesn't have the name we expect when schemas are being used.

I just installed Drupal 7 fine on PHP 5.3.2-1ubuntu4.7, PostgreSQL 8.4.7 and Apache 2.2.14 under a PostgreSQL schema called "drupal". Thats the standard build on Ubuntu 10.04 LTS.

I didn't test the user workflow however. I filled out the $databases array manually and set the prefix key to "drupal.".

Can someone else try that and see if it still doesn't work for them?

Hi,

It really looks like a schema/search patch issue.

I've a Drupal 7 default installation and recently created a test site. The default configuration relies in a database schema called 'drupal' (with the same username: 'drupal'). The test site has a new schema and username ('drupaltest'). Every time I tried to install Drupal test site I got the same response (posted by dark_ixion).

The solution was specify the schema with "prefix":

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'webd1',
      'username' => 'drupaltest',
      'password' => '*********',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'pgsql',
      'prefix' => 'drupaltb.', // Before was empty
    ),
  ),
);

Then, the installation was successfully.

Best Wishes

This seems related to #956942: Use current postgresql schema instead of hardcoded "public". . We should be settings the search path to the correct schema on connection. I recall we were looking at this but were waiting for prefixes per connection to get in first.

See other related issues: http://drupal.org/project/issues/drupal?text=search_path&status=All

To me, this issue lies in how Drupal uses prefixes rather than schemas which is a reflection of how MySQL treats schemas as databases which gives MySQL users no concept of a schema.

What that means is, a connection prefix should be renamed to a connection "schema", giving more reason to support dot syntax within a prefix. With that in place, the search_path can be set per connection based on the schema's outlined default schema if any, otherwise the search_path can be left alone.

With that in place, prefixing tables and keys would become much simpler. However, when schema details were addressed in D7, they were done before prefixes per connections were implemented

I marked #956942: Use current postgresql schema instead of hardcoded "public". , #1219674: Postgresql not public schema upgrade error, #1227004: Multiple issues with PostgreSQL driver and public schema all duplicates.

Same search also found http://drupal.org/node/1177322

We really need #1060798: Enable apachsolr 'related posts' block for issues (and forums?).

Hey guys! As far as I see, it is silence here for about a year. And all isses still in 8.x/7.15.
Nobody wants to fix this?

By the way, I have one more issue related to this topic:
When trying to install d7.15 in non-public schema (when another copy is already installed there) Drupal says "Drupal could not be correctly setup with the existing database. Revise any errors."
as far as I looken in code — error happens when drupal trying to create functions in global namespace. And it seems, that PgSQL server (9.1.4) doesn't allow to do that.

P.S. How can I help to speed up issue fixing? I'm very interested in fixing it asap.
//wbr,
mva

Hey mva.name! By all means, please jump in and fix the issue! I have literally been stuck on a project for the last 18 months using a MySQL database (ewww) and since my effort is focused there, I haven't been able to put in time to fix these PostgreSQL issues. More than happy to review any patches though!

Jumping in to see if I can shed some light on this.

This thread is tagged version 8.x-dev. From the timing of the issue and the description I would assume its not. Since I can reproduce the issue in 7.21 I've changed the thread accordingly.

I've made separate installs with minimal profile and language = English with PostgreSQL 9.2:

1. install sandbox4 in its own database with schema public
2. install sandbox4 in schema sandbox4 in database drupal

I visually compared the two databases and saw no differences (same number of tables, same records in system, menu_router and so on).

In the next step I've made a dump of both schema's:

pg_dump -U sandbox4 --schema= > sandbox4<-db>.psql

Next step was to find/replaced all instances of "Schema: public" and "public." with "Schema: sandbox4" and "sandbox." in sandbox4-db.psql to rule out all differences in the dump caused by the name of the schema.

After that I compared the two dumps: diff sandbox4.psql sandbox4-db.psql > sandboxdiff.txt. I've attached the file for further inspection.

My initial finding is there are no meaningful differences between the dumps; the binary data being shown is probably an artefact of diff.

All this leads me to the assumption we might be looking in the wrong direction: this is not an installation issue but some other bug. The most likely cause is somewhere in menu_router, since paths like /admin produce WSOD and my error log points to line 2127 in system.module:

  if (!isset($item['mlid'])) {
    $item += db_query("SELECT mlid, menu_name FROM {menu_links} ml WHERE ml.router_path = :path AND module = 'system'", array(':path' => $item['path']))->fetchAssoc();
  }

Any thoughts or comments?

To follow up on my post:

All of the reported problems disappeared when I changed the prefix from .

$databases['default']['default'] = array (
      'database' => 'drupal',
      'username' => 'sandbox',
      'password' => 'sandbox',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'pgsql',
      'prefix' => array(
     	'default'   => 'sandbox.',
     	'users'     => 'profiles.',
     	'users'     => 'profiles.',
     	'users_roles'     => 'profiles.',
     	'sessions'  => 'profiles.',
     	'role'      => 'profiles.',
     	'authmap'   => 'profiles.',
   		)
	);

Why is this issue closed? I'm having issues with my PostgreSQL Drupal site migrated from MySQL. Other sites migrated from MySQL using the schema "public" are not having issues.

One issue I'm having is my tables not dropping when I uninstall modules. I basically tracked it down to db_table_exists() which ends up in DatabaseSchema::getPrefixInfo() that is using the hardcoded value defaultSchema being set to exactly "public". This can not be changed in any way but hacking core.

Patch fails with:
Fatal error: Call to undefined method Drupal\Core\Database\Schema::construct() in /var/www/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php on line 36

If I remember right, construct() moved over to Connection.php?

It means that it should be parent::__construct() :)

I tried changing the patch to call parent::__construct(), but that caused other problems. I'll post the patch here just for reference, but I really don't expect it to solve the problem.

Yep, I think there are more hardcoded 'public' vars around and this could help but will not finally make Drupal work outside public schema. Also I don't think that this is required any more to pass tests, at least for the remaining once other than migrate - but that is thing of it's own to fix ;-) Thanks anyway @jaredsmith for the patch.

Changed the hardcoded 'public' vars for the class variable $defaultSchema.

Oeps. The class variables go first!

Sorry, back to needs work ;-) I just installed D8 outside public schema, it works with, or without the patch. However, I did just a quick test run of the Database test group and there are still fails and exceptions with patch applied.

Hi,

I am using Drupal 8.0.2. I cannot install on non-public schema. Have you guys installed?

@BORANBURCIN Is this still being the case.. Same problem here on 8.0.3

Hi,

Same problem with drupal-8.0.5. I tried to create a patch file, but I don't have much experience about patch files. I hope it's in the correct format.

Patch looks right, let's see what testing will tell

@ozden_meren: Your patch looks good. Thanks.

I think that we need to combine the patches from #38 and #44. We also need some tests to make sure that the problems are fixed and stay fixed.

Not sure why this can't be fixed in 8.1.x?

Re-roll of patch #38.

Improved patch version.

The patch looks good. I do have some remarks:

1. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
   @@ -46,6 +46,15 @@ class Schema extends DatabaseSchema {
   +    $this->defaultSchema = $prefix_info['schema'];
   

   Can we default to "public" if $prefix_info['schema'] is empty.

2. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
   @@ -101,9 +110,9 @@ public function queryTableInformation($table) {
   -    else {
   +    elseif (strpos($table, 'db_temporary_')) {
   

   Why is this change necessary?

The method Connection:: getFullQualifiedTableName() has a hard coded ‘public’.

We will also need some tests to make sure that all drupal works with a non-public schema.

Hello, I had the same issue trying to install a D8 8.3-x with a PostGreSQL schema different from the "public" one.
Using the patch in #52 works great, but due to short array notation change, this patch can't be applied automatically anymore.
So here the patch rerolled on D8 8.4-x. and some answers :

1. Default to "public" is already happening because of protected $defaultSchema = 'public'; in Schema.php
2. Don't really know but there is no "else" statement anymore and it seems a bit strange.
3. I tried to do something in the "drupal-1060476-55-bis.patch". The getFullQualifiedTableName method seems to be called in migrations only. It can be called and tested like that \Drupal::service('database')->getFullQualifiedTableName('TABLE');
4. D8 8.4-x fresh install works properly.

I faced some issues with the command "drush entity-updates", on field deletions, and more particularly on index renaming.
The new patch includes a fix for this too.

Fixing the case when INDEX needs to be dropped in a non-public schema.

Re-roll.

I think I finally figured it out, updated patch to follow soon...

Changes:

1. Updated Connection::getFullQualifiedTableName() method for PostgreSQL.
2. Made Schema::getPrefixInfo() method public because it has crucial functionality which is not accessible from Connection.
3. Made Sql::getQualifiedMapTableName() use not fully qualified table name because PostgreSQL doesn't support cross DB joins. Map table is joinable only in same DB per SqlBase::mapJoinable()
4. Minor code style/docs fixes.

This one should work.

Generated from drupal/drupal instead of drupal/core, same as #62.

Added missed MySQL schema.

Still on it, looks like we overlooked identifier name quoting in some places...

Slightly reworked version.

Main changes from previous:

* Removed strpos() detection of schema where possible, replaced with Schema::getPrefixInfo()
* Used Connection::escapeTable() for identifier quoting

Fixed unit test.

Added small performance improvement and made unit test a bit more explicit.

Thanks a lot for the last patch (drupal-1060476-73.patch)!
I applied it on my website (Drupal 4.0 - PostGreSQL 9.4.7 - PHP 5.6). No regression seen yet.
Thanks for your work.

The patch looks good. But what I am missing is tests to make sure that the problem is fixed and to make sure that future patches do break the support for non-public schema's. Kerneltest will be diffecult to do with the current testbot. So my suggestion will be to create unittests. Let the PostgreSQL driver calculate the query string and test if that will work for different schema's (public, temporary and others). Do that for SELECT, UPDATE, DELETE, INSERT and the different ALTER queries. Create the test with provider methods. See: https://phpunit.de/manual/current/en/writing-tests-for-phpunit.html#writ....

+++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
@@ -45,6 +45,19 @@ class Schema extends DatabaseSchema {
+    // Retrieve the max identifier length which is usually 63 characters
+    // but can be altered before PostgreSQL is compiled so we need to check.
+    $this->maxIdentifierLength = $this->connection->query("SHOW max_identifier_length")->fetchField();

Please, create a separate issue for this.

Thanks for your review @daffie.

I don't think that writing tests for different query types will help. They all using Connection::prefixTables so I tried to test this. Lets see what testbot will say.

Removed max_identifier_length retrieval change as requested and added prefixTable test.

@RoSk0: Lets look at this in a different way. Lets say that this patch fixes all non default schema problems for PostgreSQL. What parts of this patch can later be changed back without the testbot generating any fails. The only security we have is the testbot. So we need tests for ALL the changes that make drupal core work on non default schema's.

Reverted testComputedConstraintName changes related to #2921665: PostgreSQL: Optimize getting maxIdentifierLength.

@daffie Thanks for the suggestion, but I'm struggling to figure it out.
There is a major issue with testing this patch, at least in my understanding, major changes are in
Schema::queryTableInformation() and Schema::renameTable(). In Schema::queryTableInformation() we fixing logic and to test the outcome we need to actually talk to DB. In Schema::renameTable() we adding schema name to queries and same thing with testing only possible by real DB queries.

So in my opinion we only can test it indirectly by testing Connection::getFullQualifiedTableName() which has hard-coded
"public" schema at the moment and producing "drupal8-contrib.public.drupal.cache" instead of "drupal8-contrib.drupal.cache".

I'm open to suggestions on how to improve test coverage for this.

Wake up testbots

Ok, so my assumption that testComputedConstraintName will work was wrong, fixing...

Re-applied fix to testComputedConstraintName.
Fingers crossed.

Setting back to "Needs review" as tests passed, removing "Needs test" tag as tests were added.

I confirm that the patch works properly.
Patching Drupal 8.5.3 and using a non-public schema (multisite with one schema per site) on PostGreSQL 9.5 works with #85.
Without the patch, the installation fails when using prefixes like "siteA.", "siteB.", etc.
Thanks RoSK0 !

The patch looks good and @Renrhaf has manual tested the patch with a non-public schema. That is great. I do have 2 minor remarks about this patch:

1. The testbot is failing Drupal/Tests/Core/Database/Driver/pgsql/PostgresqlSchemaTest.php for MySQL. This is wrong and needs to be fixed. See: https://www.drupal.org/pift-ci-job/805984.
2. You are making a lot of changes to the test Drupal/Tests/Core/Database/Driver/pgsql/PostgresqlSchemaTest. I find it difficult to see what changes are necessary for this patch and what changes are for making the test more pretty. Could you remove are the make the test more pretty changes.

@RoSkO: Great work on this patch!

We do not have a subsystem maintainer any more after @crell left. So changing it to the framework maintainer.

I think that test failure is using the test-only patch and not the real patch in #85, @daffie.

diff --git a/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php b/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php
index 6bdd51ebf0..77ccbf659e 100644
--- a/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php
+++ b/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php
@@ -265,7 +265,23 @@ public function messageTableName() {
    *   The fully qualified map table name.
    */
   public function getQualifiedMapTableName() {
-    return $this->getDatabase()->getFullQualifiedTableName($this->mapTableName);
+    $qualifiedMapTableName = NULL;
+    $connection = $this->getDatabase();
+
+    switch ($connection->databaseType()) {
+      case 'pgsql':
+        // PostgreSQL doesn't support cross DB merges. So only use schema and
+        // prefixed table name.
+        $info = $connection->schema()->getPrefixInfo($this->mapTableName);
+        $qualifiedMapTableName = $info['schema'] . '.' . $info['table'];
+        break;
+
+      default:
+        $qualifiedMapTableName = $connection->getFullQualifiedTableName($this->mapTableName);
+        break;
+    }
+
+    return $qualifiedMapTableName;
   }

I think that this may get rejected on further review as it's been frowned upon to add in driver-specific options in core _not_ in the driver itself. I think it would be possible to work around this by adding an optional parameter to getFullQualifiedTableName?

diff --git a/core/tests/Drupal/Tests/Core/Database/Driver/pgsql/PostgresqlSchemaTest.php b/core/tests/Drupal/Tests/Core/Database/Driver/pgsql/PostgresqlSchemaTest.php
@@ -44,23 +44,34 @@ protected function setUp() {
     $statement = $this->getMock('\Drupal\Core\Database\StatementInterface');
     $statement->expects($this->any())
       ->method('fetchField')
-      ->willReturn($max_identifier_length);
+      ->will($this->onConsecutiveCalls($max_identifier_length, 1));

I don't think this is related to the patch, @daffie. I think this and some of the changes in PostgresqlSchemaTest are because there are additional calls to fetchField for checking indexes. It is because the old code should pass because the query that checks for indexes is truthy so it "works".

The testbot fixed my first remark from comment #92. For my second remark, I did take the time to have another good look at the test and I understand the changes now, but I do have a new point about the test:

+++ b/core/tests/Drupal/Tests/Core/Database/Driver/pgsql/PostgresqlSchemaTest.php
@@ -44,23 +44,34 @@ protected function setUp() {
+    $this->connection->expects($this->any())
+      ->method('escapeTable')
+      ->willReturnMap([
+        ['user_field_data____pkey' , '"user_field_data____pkey"'],
+        ['user_field_data__name__key', '"user_field_data__name__key"'],
+        ['drupal_BGGYAXgbqlAF1rMOyFTdZGj9zIMXZtSvEjMAKZ9wGIk_key', '"drupal_BGGYAXgbqlAF1rMOyFTdZGj9zIMXZtSvEjMAKZ9wGIk_key"'],
+      ]);

I have a problem with this part. The entire idea of testing with a provider method is that you can add extra testcases to the provider method,
without changing the main test method. Only by adding the above codeblock, that is no longer possible. I do not think we can remove this codeblock from the test method. Therefor my suggestion would be to change this test to a kernel test. There you have a normal database connection and there is no need mocking used classes.

Looks like this might be the bug that bit me today.

Looks like this might be the bug that bit me today.

@kattekrab: Would you be able to test the patch from comment #85 and see if it fixes your problem. User @Renrhaf already confirmed that the patch worked for him. The only work on this patch is a test. The rest of this patch should work.

Hi,

I confirm that the patch from comment #85 is working on a non-public schema with Drupal 8.5.3.
Tested on multiple environments.

I agree that a kernel test would be useful. I confirmed in drupalci_environments that the database user is the database owner, however this may not always be the case for local development / testing environments. So if we're okay with possibly failing test runs on non-drupal.org systems, then that would make sense.

The current unit test could be modified to use the data provider because the test arguments are the same as what we're mocking, @daffie.

Back to "Needs review".

I don't see any benefit in conversion to Kernel test unless we actually will test on non-public schema, but as far as I can tell we can't specify what schema to use when testing on PostgreSQL.

Rerolled against 8.7.x

Here I have re-rolled the patch.

@ravi.shankar: Thanks for the reroll.

To be able to test a patch for this issue a new testbot with a non-public schema is needed. Postponing untill there is such a testbot. See: #3104007: Postgresql 12 DrupalCI environment needed for core testing.

+++ b/core/tests/Drupal/Tests/Core/Database/Driver/pgsql/PostgresqlConnectionTest.php
@@ -116,4 +116,65 @@ public function testEscapeField($expected, $name) {
+    $pgsql_connection   = new Connection($this->mockPdo, $connection_options);
...
+    $pgsql_connection   = new Connection($this->mockPdo, $connection_options);

There's an extra space on the left-hand side of the assignment operator.

I got this before patching:

$ drush st
In Connection.php line 695:
  SQLSTATE[42602]: Invalid name: 7 ERROR:  invalid name syntax
  LINE 6: AND pg_attribute.attrelid = '.my_postgres_db.cache_config'::regcl...
                                        ^: SELECT pg_attribute.attname AS column_name, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type, pg_get_expr(pg_attrdef.adbin, pg_attribute.attrelid) AS column_default
  FROM pg_attribute
...
In Statement.php line 59:

  SQLSTATE[42602]: Invalid name: 7 ERROR:  invalid name syntax
  LINE 6: AND pg_attribute.attrelid = '.my_postgres_db.cache_config'::regcl...

Applying the patch 1060476-105.patch fixes it.

Also, if I try to define the prefix with a traling dot during installation (in Lando) like this drush site:install --db-url=pgsql://postgres:@database/drupal8 --db-prefix=my_postgres_db. -y I get an error:

In install.core.inc line 1182:
To start over, you must empty your existing database and copy <em>default.settings.php</em> over.

So I have to run drush site:install with --db-prefix=my_postgres_db and manually add the dot to the prefix in settings.php afterwards, like this:

$databases['default']['default'] = array (
  'database' => 'drupal8',
  'username' => 'postgres',
  'password' => '',
  'prefix' => 'my_postgres_db.',
  'host' => 'database',
  'port' => '',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\pgsql',
  'driver' => 'pgsql',
);

We have now testbots for PostgreSQL 10 and 12. Both have an extra schema called: testing_fake. Because all PostgreSQL testbots must pass the added testing, I move this issue to 9.1.

To my luck, the table in the database-dump I was importing has since changed from my_postgres_db to the generic public, which made defining prefix with 'prefix' => 'my_postgres_db.', no longer necessary.

Rerolled patch for 9.1.x. Kindly review.

In reply to #109: I believe your issue is related to bug in Drush described here https://github.com/drush-ops/drush/issues/1523 with fix here https://github.com/drush-ops/drush/pull/2567 .

Thanks for working on supporting non-public schema for PostgreSQL, and sharing the Github issue @RoSk0! I hope it gets committed at some point.

Still needs work to address:

• #108
• convert test to functional and use real DB as per #94 (see also #107 & #110)

#113 doesn't apply cleanly to 9.1.8, lib/Drupal/Core/Database/Driver/pgsql/Schema.php.rej is attached but I can't see any reason why this doesn't apply

Hi @intrafusion, that you for the reroll!. At the moment is your patch not working. A patch file should have the extension ".patch". Also when you are working with multiple patches it helps when the naming of the patch has the issue ID in it followed by its comment ID. See: https://www.drupal.org/docs/develop/git/using-git-to-contribute-to-drupa.... With which Drupal core branch are you working?

@daffie, it's not my patch, it's the patch from #113

I posted the rejected file that was generated when I tried to apply the patch. I've compared the line by line changes and cannot understand why the patch doesn't apply correctly

Re-roll patch given for 9.3.x.

Fixing 'custom command fails' Issues.

Reviewing Bug Smash issues. I don't know db drivers well so can not comment on the actual fix or the tests here. I did not try to the steps to reproduce because they are for Drupal 7. The Issue Summary does not provide a list of tasks for a proposed resolution. So, even if I knew more about db drivers it is not possible to know if this patch is implementing the recommended fix. Tagging for issue summary update.

There were several rerolls since #105, none of which provided a diff. Those diffs are added.
@ridhimaabrol24 and @vsujeetkumar It is good practice to always add a interdiff or diff. It makes the task of the reviewer much easier.

1. +++ b/core/lib/Drupal/Core/Database/Driver/mysql/Schema.php
   @@ -44,10 +44,10 @@ class Schema extends DatabaseSchema {
   +   * @return array
   

   Out of scope change

2. +++ b/core/lib/Drupal/Core/Database/Driver/mysql/Schema.php
   @@ -44,10 +44,10 @@ class Schema extends DatabaseSchema {
   +  public function getPrefixInfo($table = 'default', $add_prefix = TRUE) {
   

   Why this change? Why does mysql have to change?

3. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
   @@ -202,14 +212,12 @@ protected function getTempNamespaceName() {
   +   * @param string $table
   

   Out of scope

4. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
   @@ -461,7 +469,7 @@ public function getFieldTypeMap() {
   +    ];
   

   Out of scope

5. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
   @@ -727,7 +741,12 @@ public function indexExists($table, $name) {
   +    $schema = $info['schema'];
   +    $table_name = $info['table'];
   +    return (bool) $this->connection->query("SELECT 1 FROM pg_indexes WHERE schemaname = :schema AND tablename = :table AND indexname = :index", [':schema' => $schema, ':table' => $table_name, ':index' => $index_name])->fetchField();
   

   Line too lone, put the array on multiple lines. And we can avoid the two variables if we use $info['schema'] and $iinfo['table'] in the query string.

6. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
   @@ -850,7 +869,13 @@ public function dropIndex($table, $name) {
   +    // Add the schema if not using the default one.
   

   When there is an 'if' in a comment there is usually one in the code as well. What happens here when the default schema is used? It is not clear to me.

7. +++ b/core/lib/Drupal/Core/Database/Schema.php
   @@ -81,7 +81,7 @@ public function nextPlaceholder() {
   +  public function getPrefixInfo($table = 'default', $add_prefix = TRUE) {
   

   As above why is this needed?

8. +++ b/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php
   @@ -279,7 +279,23 @@ public function messageTableName() {
   +    $qualifiedMapTableName = NULL;
   

   Not needed, the switch has a default case where it is set.

Updated the IS.

@quietone: Thank you for the feedback!

Updated patch for last 9.3.x (Test class didn't apply).
Including quietone remarks.

In relation to #7 in #1060476-125: Multiple issues when PostgreSQL is used with non-public schema by not making getPrefixInfo public I get the following error when using migrations:

Error: Call to protected method Drupal\Core\Database\Schema::getPrefixInfo() from context 'Drupal\migrate\Plugin\migrate\id_map\Sql' in Drupal\migrate\Plugin\migrate\id_map\Sql->getQualifiedMapTableName() (line 288 of core/modules/migrate/src/Plugin/migrate/id_map/Sql.php) #0 modules/contrib/migrate_tools/src/MigrateExecutable.php(128): Drupal\migrate\Plugin\migrate\id_map\Sql->getQualifiedMapTableName()

Changing this function from protected to public fixes this error and allows the migrations to complete

Patch attached which adds back #7 from #125, no idea how to create an interdiff

Also re-rolled for 9.3 as it was no longer applying cleanly, no idea about 9.4 yet

The tests from the proposed solution still needs to be added.

Patch for 9.4 as the files have been moved into separate modules as per https://www.drupal.org/node/3129492

Last patch wasn't applying for 9.4 due to this file that has been moved core/tests/Drupal/Tests/Core/Database/Driver/pgsql/PostgresqlSchemaTest.php.

Updating patch

Fix incorrect patch uploaded for 9.4

@tostinni: Great to see that you are working on this issue. I shall help by reviewing your work. You can contact me on drupal.slack.com in the #contribute channel. My username there is the same as here.

The first goal is to get this fixed in 10.1.x. Later we maybe able to backport this to earlier Drupal versions.
The testbot for PostgreSQL has a special schema to make this issue testable and the schema name is testing_fake.
The issue summery has a list of tests that are needed. It is a long list, only they are all very simple test. It is less work than it looks.

I have added the tag "Needs framework manager review" 4 years ago and I added it, because there was no subsystem maintainer for the Database API. As I am now that subsystem maintainer and I added the tag, makes me free to remove it. Not adding back the tag "Needs subsystem maintainer review", because as that subsystem maintainer, I think this issue should be fixed.

Thanks all. I am getting this error with #138.

PHP Fatal error:  Access level to Drupal\mysql\Driver\Database\mysql\Schema::getPrefixInfo() must be public (as in class Drupal\Core\Database\Schema) in web/core/modules/mysql/src/Driver/Database/mysql/Schema.php on line 50

Updating the patch for 9.4.x

Trying again for 9.4.x and 9.5.x

I created a new patch based on the patch from #141 which should be working on 10.1.x dev. This patch contains most or all test in the proposed resolution and is made in collaboration with @daffie.