Ensures plpgsql extension is installed

What does this MR do and why?

Describe in detail what your merge request does and why.

Solves #325790 (closed) partially

It ensures that plpgsql extension is installed through structure.sql and init_structure.sql. If this extension is unavailable, database migrations will fail when the customers upgrade.

Local test results:

Using Postgres -v 11.7

rails db:structure:load -v

...
******************************************************************************
  You are using PostgreSQL 11.7 for the main database, but PostgreSQL >= 12
  is required for this version of GitLab.
...
******************************************************************************
...
******************************************************************************
  You are using PostgreSQL 11.7 for the ci database, but PostgreSQL >= 12
  is required for this version of GitLab.
...
******************************************************************************

psql:/Users/leonardodarosa/gitlab-development-kit/gitlab/db/structure.sql:13: NOTICE:  extension "plpgsql" already exists, skipping
psql:/Users/leonardodarosa/gitlab-development-kit/gitlab/db/ci_structure.sql:13: NOTICE:  extension "plpgsql" already exists, skipping

Postgres -v 12.2

rails db:structure:load -v

psql:/Users/leonardodarosa/gitlab-development-kit/gitlab/db/structure.sql:13: NOTICE:  extension "plpgsql" already exists, skipping
psql:/Users/leonardodarosa/gitlab-development-kit/gitlab/db/ci_structure.sql:13: NOTICE:  extension "plpgsql" already exists, skipping

Screenshots or screen recordings

How to recreate issue locally

Reproduce these steps on master

1. Set a Postgres version using asdf

asdf install postgres 11.7

2. Start the server

/Users/YourUser/.asdf/installs/postgres/11.7/bin/pg_ctl -D /Users/YourUser/.asdf/installs/postgres/11.7/data -l logfile start

3. Set the installed version in current shell session

asdf shell postgres 11.7

4. Change your database.yml to use a temporary database

development:
  main:
    database: schema_test_main
    host: localhost
ci:
  main:
    database: schema_test_ci
    host: localhost

5. Temporarily disable database.yml test section:

#test: &test
#  main:
#    adapter: postgresql
...
#  ci:
#    adapter: postgresql
...

6. Create the temp databases through psql:

psql -U postgres postgres

CREATE DATABASE schema_test_main;
CREATE DATABASE schema_test_ci;

or use Rails

rails db:create:main && rails db:create:ci

Created database 'schema_test_main'
Created database 'schema_test_ci'

7. Ensure plpgsql extension is disabled in both databases, schema_test_main and schema_test_ci

psql

\c schema_test_main
You are now connected to database "schema_test_main" as user "youruser".

\dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

DROP EXTENSION IF EXISTS plpgsql CASCADE;
DROP EXTENSION

\dx
     List of installed extensions
 Name | Version | Schema | Description
------+---------+--------+-------------
(0 rows)

8. Try to migrate the db. An error should happen

using structure.sql

bundle exec rails db:structure:load

psql:/Users/leonardodarosa/gitlab-development-kit/gitlab/db/structure.sql:25: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
rails aborted!
failed to execute:
psql --set ON_ERROR_STOP=1 --quiet --no-psqlrc --file /Users/leonardodarosa/gitlab-development-kit/gitlab/db/structure.sql --single-transaction schema_test_main

Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions.

/Users/leonardodarosa/gitlab-development-kit/gitlab/lib/gitlab/database/postgresql_database_tasks/load_schema_versions_mixin.rb:10:in `structure_load'
Tasks: TOP => db:schema:load
(See full trace by running task with --trace)

using init_structure.sql

rails db:migrate

PG::UndefinedObject: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
/Users/leonardodarosa/gitlab-development-kit/gitlab/db/migrate/20210602155110_init_schema.rb:7:in `up'
...

How to set up and validate locally

Using db/structure.sql

Change to this current branch and follow steps 1 to 7 from How to recreate issue locally

1. Run bundle exec rails db:structure:load
2. Schema should be fully loaded. You can check it by connecting to the rails console: bundle exec rails c
3. Check if the 3 required extensions are installed

\dx
                                     List of installed extensions
    Name    | Version |   Schema   |                            Description
------------+---------+------------+-------------------------------------------------------------------
 btree_gist | 1.5     | public     | support for indexing common datatypes in GiST
 pg_trgm    | 1.4     | public     | text similarity measurement and index searching based on trigrams
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language

Using db/init_structure.sql

Change to this current branch and follow steps 1 to 7 from How to recreate issue locally

init_structure.sql is invoked through 20210602155110_init_schema.rb

1. Run bundle exec rails db:migrate
2. DB migration should be done. You can check them by connecting to the rails console: bundle exec rails c
3. Check if the 3 required extensions are installed

\dx
                                     List of installed extensions
    Name    | Version |   Schema   |                            Description
------------+---------+------------+-------------------------------------------------------------------
 btree_gist | 1.5     | public     | support for indexing common datatypes in GiST
 pg_trgm    | 1.4     | public     | text similarity measurement and index searching based on trigrams
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language

4. Stop postgres service

/Users/YourUser/.asdf/installs/postgres/11.7/bin/pg_ctl -D /Users/YourUser/.asdf/installs/postgres/11.7/data -l logfile stop

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #325790 (closed)