Create the pg_trgm extension on PostgreSQL 10.12 and 12.1 database environments [#3186676]

Problem/Motivation

#3104007: Postgresql 12 DrupalCI environment needed for core testing added/installed the pg_trgm extension on PostgreSQL 10 and 12. The next step is to create the extension in the database, only the database user has to have superuser privileges. The testbot database user does not have those privileges, therefore the extension must be created in the drupalci_environment. This is needed to make the solution for #2988018: [PP-1] Performance issues with path alias generated queries on PostgreSQL testable.

Proposed resolution

Created the pg_trgm extension in the database.

Remaining tasks

Manual testing/Review

Manual testing

Setup inside a drupalci vagrant VM

TODO: Point to the documentation for what a drupalci vagrant VM is, and how to install it

(Do NOT perform the following tests in your host environment, as they will delete all of your pre-existing docker containers.)

Clone drupalci_environments
Apply the patch inside of it
cd ~/drupalci_environments/db/pgsql-10.12
docker build . (if successful, this will return a line like "Successfully built 7d7cfc3ec4ac")
docker tag 7d7cfc3ec4ac drupalci/pgsql-10.12 (7d7cfc3ec4ac comes from the previous line)
Do the same for PostgreSQL 12.1

Manually Test schema and extension output

for IMAGE in 10.12 12.1 ; do
docker run -d  drupalci/pgsql-${IMAGE} ;
sleep 20;
docker exec -ti $(docker ps -q) psql -d drupaltestbot -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_trgm'" ;
docker rm -f $(docker ps -q) ;
done

The output for those exec commands should look like this given 10.12 and 12.1

  name   | default_version | installed_version |                              comment                              
---------+-----------------+-------------------+-------------------------------------------------------------------
 pg_trgm | 1.3             | 1.3               | text similarity measurement and index searching based on trigrams
(1 row)

Release notes snippet

DrupalCI now has the pg_tgrm extension created on the PostgreSQL versions 10 and 12 database environments .

Comment	File	Size	Author
#2	3186676-2.patch	1.59 KB	daffie

Issue fork drupalci_environments-3186676

Show commands

Start within a Git clone of the project using the version control instructions.

Add & fetch this issue fork’s repository

Or, if you do not have SSH keys set up on git.drupalcode.org:

Add & fetch this issue fork’s repository

3186676-enable-pg_trgm changes, plain diff MR !2
Check out this branch for the first time

Check out existing branch, if you already have it locally

About issue forks

Comments

Comment #1

5 December 2020 at 12:14

daffie created an issue. See original summary.

Comment #2

daffie commented 5 December 2020 at 12:15

Status:

Active

» Needs review

Status	File	Size
new	3186676-2.patch	1.59 KB

Added the patch.

Comment #3

daffie commented 5 December 2020 at 12:19

Issue summary:	View changes
Related issues:		+#2988018: [PP-1] Performance issues with path alias generated queries on PostgreSQL

Comment #4

daffie commented 5 December 2020 at 16:08

According to the PostgreSQL manual:

Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script.

See: https://www.postgresql.org/docs/10/sql-createextension.html

Comment #5

jweowu commented 18 May 2021 at 12:34

The test script given worked for me, with one hiccup -- the first time through I got the following failure:

$ for IMAGE in 10.12 12.1 ; do
> docker run -d  drupalci/pgsql-${IMAGE} ;
> sleep 20;
> docker exec -ti $(docker ps -q) psql -d drupaltestbot -c "SELECT COUNT(*) FROM pg_extension WHERE extname = 'pg_trgm'" ;
> docker rm -f $(docker ps -q) ;
> done
2c53b8d6728723460e468cadc35c55f700a44925a7b6440598da34125f2f872d
OCI runtime exec failed: exec failed: container_linux.go:367: starting container process caused: exec: "455db3904427": executable file not found in $PATH: unknown
2c53b8d67287
455db3904427
8d0cecdf9dc2
bd8888e01707
997acf7fdf10
b1ef24283728
c49770635897
7f98f44fe979
ff7d079c9680
179e8bf67634f45b180d37b010a87f02269f069e85bc4d058fc42b29f97d444b
 count 
-------
     1
(1 row)

179e8bf67634

I'm guessing that whatever that sleep 20 is for simply needed more time, as additional runs were successful:

$ for IMAGE in 12.1 ; do docker run -d  drupalci/pgsql-${IMAGE} ; sleep 20; docker exec -ti $(docker ps -q) psql -d drupaltestbot -c "SELECT COUNT(*) FROM pg_extension WHERE extname = 'pg_trgm'" ; docker rm -f $(docker ps -q) ; done
f97a9d46a6dcb924094776812f265aa178fef02b72d4adea0b2941116ea8aa02
 count 
-------
     1
(1 row)

f97a9d46a6dc

$ for IMAGE in 10.12 ; do docker run -d  drupalci/pgsql-${IMAGE} ; sleep 20; docker exec -ti $(docker ps -q) psql -d drupaltestbot -c "SELECT COUNT(*) FROM pg_extension WHERE extname = 'pg_trgm'" ; docker rm -f $(docker ps -q) ; done
12e25c04a210abbe110c8b69180ea60af435f63e476b5603a1fdd5ad07cd933f
 count 
-------
     1
(1 row)

12e25c04a210

$ for IMAGE in 10.12 12.1 ; do docker run -d  drupalci/pgsql-${IMAGE} ; sleep 20; docker exec -ti $(docker ps -q) psql -d drupaltestbot -c "SELECT COUNT(*) FROM pg_extension WHERE extname = 'pg_trgm'" ; docker rm -f $(docker ps -q) ; done
d9f5554d52bec5be41cf27c1bda3eb647c4966e7210c948be4d3990bd0de2e1d
 count 
-------
     1
(1 row)

d9f5554d52be
9f9a07601e030ae345560b54bacfecc27a5d11f9d762c1106d40a8200e9305f9
 count 
-------
     1
(1 row)

9f9a07601e03