Automatically install pg_trgm extension on PostgreSQL 13+

Just to kick things off, I sketched up a simple patch that demonstrates a potential approach to this. I don't know if it's the best place to put this, and I have NOT tested it with a fresh install yet. We'll also need tests, of course... but I figure it's worth getting a quick sanity check from others before spending time on it. :-)

1. +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Install/Tasks.php
   @@ -298,6 +298,11 @@ public function initializeDatabase() {
   +      if (version_compare($connection->version(), 13, '>=')) {
   

   Can we change 13 to '13.0.0'. That is how the function version_compare() works. See: https://www.php.net/manual/en/function.version-compare.php

2. Can we add a @todo to remove the if-statement in Drupal 11. The minimum required version for PostgreSQL will become 14.

@m.stenta: Good find! Thanks.

added updated patch and address #3

+++ b/core/modules/pgsql/src/Driver/Database/pgsql/Install/Tasks.php
@@ -298,6 +298,15 @@ public function initializeDatabase() {
+      /**
+       * @todo Remove this if-statement in Drupal 11 when the minimum required version for
+       * PostgreSQL becomes 14.
+       */

Can we change this to:

       // @todo Remove this if-statement in Drupal 11 when the minimum required version for
       //  version for PostgreSQL becomes 13 or higher.

address comment #5

@Akram Khan: I have updated my comment #5. Thank you for responding so fast to my review. It was a bit too fast. ;) Could you update the patch a second time?

yes @daffie sry for that address now

I have asked to the testbot maintainer @hestenet on Drupal Slack for a testbot with PostgreSQL 12 and PHP 8.1. As this is a fix for PostgreSQL 13 and higher, we should make sure that it works with PostgreSQL 12. As for Drupal 10 the minimum required version for PostgreSQL is 12.

+++ b/core/modules/pgsql/src/Driver/Database/pgsql/Install/Tasks.php
@@ -298,6 +298,13 @@ public function initializeDatabase() {
+      // @todo Remove this if-statement in Drupal 11 when the minimum required version for

Could you remove "version for" from the end of the line. Thank for working on this issue.

sry for all the wrong file @daffie now address your comment

Great to see this moving!

Adding the "Needs tests" flag, because I assume that is needed. :-)

@m.stenta: Good find! Thanks.

Credit where credit is due... @wotnak described this in #3270558: Install and create the postgres pg_trgm extension in docker container → starting with this comment: https://www.drupal.org/project/farm/issues/3270558#comment-14958332 →

@daffie are you able to add @wotnak to the credits on this issue? I think he should be included.

Adding the "Needs tests" flag, because I assume that is needed. :-)

How would we go about testing this, I wonder... ?

I found this related issue: #3186676: Create the pg_trgm extension on PostgreSQL 10.12 and 12.1 database environments →

... which explicitly enables the pg_trgm in drupal.org testing environments for PostgreSQL 10.12 and 12.1 environments.

It looks like the PostgreSQL 13.5 and 14.1 environments are enabling it as well:

• https://git.drupalcode.org/project/drupalci_environments/-/blob/1097cf87...
• https://git.drupalcode.org/project/drupalci_environments/-/blob/1097cf87...

I suppose the best way to test this would be to remove those lines from the 13.5 and 14.1 startup.sh scripts, and see if the tests still pass. Can't do that... because it would cause all other tests on those environments to start failing without this patch.

Although... that's assuming that Drupal core has automated tests that would fail if the pg_trgm extension is not enabled. I assume it would, but I haven't confirmed...

Bit of a chicken and egg problem? :-)

Shout out to hestenet for adding the new postgre test combos

I'm seeing all green and believe that's what was needed.

daffie → credited hestenet → .

Giving @hestenet issue credit for setting up the testbot with PHP 8.1 en PostgreSQL 12.

daffie → credited wotnak → .

Giving @wotnak issue credits for his work in #3270558: Install and create the postgres pg_trgm extension in docker container → .

1. +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Install/Tasks.php
   @@ -298,6 +298,13 @@ public function initializeDatabase() {
   +      // @todo Remove this if-statement in Drupal 11 when the minimum required
   ...
        catch (\Exception $e) {
   

   @todo statements should have a link to an issue, see @todo: To Do notes → . That helps them be found and reduce the risk of duplicates. There isn't an issue for this yet, as can be seen in 🌱 [11.x] [meta] Set Drupal 11 platform and browser requirements at least six months before the release Active .

2. +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Install/Tasks.php
   @@ -298,6 +298,13 @@ public function initializeDatabase() {
   +      // If we are running PostgreSQL >= 13, enable the pg_trgm extension.
   

   This can be simpler, "Enable pg_trgrm for PostgreSQL 13 or higher."

3. +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Install/Tasks.php
   @@ -298,6 +298,13 @@ public function initializeDatabase() {
   +      if (version_compare($connection->version(), '13.0.0', '>=')) {
   

   This is comparing with '13.0.0' but the PostgreSQL docs state that the version numbers are 2 digits. And this is supported by the lists of versions at https://www.postgresql.org/docs/release/13.0/. I use ddev and the installed version was "13.10 (Debian 13.10-1.pgdg110+1)" which is also two digits. So, if I was running 13.0 this would return false and the extension would not be installed. If it really is two digits then this should be changed.

The comments in #20 by quietone should be addressed with this patch.

As stated in #20.3 the versions since postgre 10 are now 2 digits. for example "13.10" or "15.2". So I've adjusted this check.

All points of @quietone have been addressed.
Back to RTBC.

I have tested this manually and I am not sure how is this supposed to work? When I create a database manually (without pg_trgm), and try to install Drupal with this patch, then on the first form submit (on the setup database step) I get an error:

Error message
Resolve all issues below to continue the installation. For help configuring your database server, see the installation handbook, or contact your hosting provider.

• The pg_trgm PostgreSQL extension is not present. The extension is required by Drupal 10 to improve performance when using PostgreSQL. See Drupal database server requirements for more information.

If I submit the form second time, the error is gone and the installation will continue. Is it possible that the extension is created only after the requirements check triggered the error already? See here:

class Tasks extends InstallTasks {
  ...

  /**
   * Constructs a \Drupal\pgsql\Driver\Database\pgsql\Install\Tasks object.
   */
  public function __construct() {
    ...

    $this->tasks[] = [
      'function' => 'checkExtensions',
      'arguments' => [],
    ];
    $this->tasks[] = [
      'function' => 'initializeDatabase',
      'arguments' => [],
    ];
  }

Tested on PostgreSQL 13, with non-superuser account.

Thanks for testing manually @poker10. Maybe the d.o automated tests don't pick this up because they work differently than a UI-based install?

I imagine checkExtensions() needs to check the PostgreSQL version as well, and only raise the error on versions less than 13.

@poker10: Thanks!

The following patch moves the code to enable the extension to the checkExtensions function. The code only runs once during site installation so it doesn't matter that we put it here. Also it wont enable the extension if it already exist.

Moved the creation of the extension to before the checking of the extension exists.
Back to RTBC.

Tested this manually with unprivileged user and installation on both databases (with and without the extension enabled) completed successfully. +1 for this patch. Thanks!

Committed and pushed c9412349f8 to 11.x (10.2.x). Thanks!

Could use release note or CR

Added CR: https://www.drupal.org/node/3368754 →

Not sure it is worth a release note, it only affects new installs.

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