Dbal: PostgreSQL Extensions support

[Knudian]

Feature Request

| Q | A
|------------ | ------
| New Feature | yes
| RFC | yes/no
| BC Break | yes/no

Summary

Since PostgreSQL can have a lot of extensions, it would be great if, while setting up DBAL, we could provide a list of extensions in use within the database.

This configuration would be implemented the package configuration :

# In a Symfony 4 project
# [project]/config/packages/doctrine.yaml 
doctrine:
    dbal:
        extensions:
            - pgstattuple
            - pg_prometheus
            - timescaledb
````

---

For information, I have all those available on a simple instance :
```txt
        name        | default_version | installed_version |                               comment                                
--------------------+-----------------+-------------------+----------------------------------------------------------------------
 pgstattuple        | 1.5             |                   | show tuple-level statistics                                          
 bloom              | 1.0             |                   | bloom access method - signature file based index                     
 pgrowlocks         | 1.2             |                   | show row-level locking information                                   
 autoinc            | 1.0             |                   | functions for autoincrementing fields                                
 dict_xsyn          | 1.0             |                   | text search dictionary template for extended synonym processing      
 intarray           | 1.2             |                   | functions, operators, and index support for 1-D arrays of integers   
 amcheck            | 1.0             |                   | functions for verifying relation integrity                           
 uuid-ossp          | 1.1             |                   | generate universally unique identifiers (UUIDs)                      
 pg_stat_statements | 1.6             |                   | track execution statistics of all SQL statements executed            
 xml2               | 1.1             |                   | XPath querying and XSLT                                              
 lo                 | 1.1             |                   | Large Object maintenance                                             
 dict_int           | 1.0             |                   | text search dictionary template for integers                         
 intagg             | 1.1             |                   | integer aggregator and enumerator (obsolete)                         
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language                                         
 moddatetime        | 1.0             |                   | functions for tracking last modification time                        
 chkpass            | 1.0             |                   | data type for auto-encrypted passwords                               
 refint             | 1.0             |                   | functions for implementing referential integrity (obsolete)          
 timetravel         | 1.0             |                   | functions for implementing time travel                               
 btree_gin          | 1.2             |                   | support for indexing common datatypes in GIN                         
 hstore             | 1.4             |                   | data type for storing sets of (key, value) pairs                     
 isn                | 1.1             |                   | data types for international product numbering standards             
 seg                | 1.1             |                   | data type for representing line segments or floating-point intervals 
 earthdistance      | 1.1             |                   | calculate great-circle distances on the surface of the Earth         
 tablefunc          | 1.0             |                   | functions that manipulate whole tables, including crosstab           
 dblink             | 1.2             |                   | connect to other PostgreSQL databases from within a database         
 file_fdw           | 1.0             |                   | foreign-data wrapper for flat file access                            
 citext             | 1.4             |                   | data type for case-insensitive character strings                     
 pg_freespacemap    | 1.2             |                   | examine the free space map (FSM)                                     
 ltree              | 1.1             |                   | data type for hierarchical tree-like structures                      
 pageinspect        | 1.6             |                   | inspect the contents of database pages at a low level                
 fuzzystrmatch      | 1.1             |                   | determine similarities and distance between strings                  
 cube               | 1.2             |                   | data type for multidimensional cubes                                 
 pg_prewarm         | 1.1             |                   | prewarm relation data                                                
 unaccent           | 1.1             |                   | text search dictionary that removes accents                          
 pg_buffercache     | 1.3             |                   | examine the shared buffer cache                                      
 adminpack          | 1.1             |                   | administrative functions for PostgreSQL                              
 tcn                | 1.0             |                   | Triggered change notifications                                       
 pgcrypto           | 1.3             |                   | cryptographic functions                                              
 insert_username    | 1.0             |                   | functions for tracking who changed a table                           
 pg_visibility      | 1.2             |                   | examine the visibility map (VM) and page-level visibility info       
 postgres_fdw       | 1.0             |                   | foreign-data wrapper for remote PostgreSQL servers                   
 tsm_system_time    | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit     
 btree_gist         | 1.5             |                   | support for indexing common datatypes in GiST                        
 tsm_system_rows    | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit           
 pg_trgm            | 1.3             |                   | text similarity measurement and index searching based on trigrams    
 sslinfo            | 1.2             |                   | information about SSL certificates                                   
 pg_prometheus      | 0.2.1           |                   | Prometheus metrics for PostgreSQL                                    
 timescaledb        | 1.2.0           | 1.2.0             | Enables scalable inserts and complex queries for time-series data    

[Ocramius]

This is up for manual DB schema migrations to perform: not for something like Doctrine DBAL, which is an abstraction layer supposed to remove (as best as it can) differences between relational database connectors.

[Knudian]

I did not ask to implement all functionnalities from those extensions.

In my humble mind (sorry if it was not clear), it's much more avoiding the PostgreSqlPlatform to fail doing anything, because of some namespaces/tablespaces in the database.

For exemple, a condition NOT LIKE '%timescaledb\_%' will avoid all tables related to TimescaleDB, allowing the user to use DBAL, and taking care of the extension usage within the application code (or in a sidecar).

If not, then a complete documentation for adding support of a specific extension would do the job.
And the support of the PostGIS extension should be removed at the same time, no ?

[Ocramius]

then a complete documentation for adding support of a specific extension would do the job

DB-side extensions are exactly what the naming states: DB-side. They shouldn't leak into DAL tooling.

And the support of the PostGIS extension should be removed at the same time, no ?

Yes, most likely. If we added them then we can't remove them due to BC compliance, but overall it is a mistake to couple the DAL to more DB specifics.

[Knudian]

I did the job, by overriding some classes (thanks to the autoload.psr-4 in composer.json)

{
    "autoload": {
        "psr-4": {
            "App\\": "src/",
            "Doctrine\\DBAL\\": "lib/dbal/"
        }
    }
}

And then by updating :

• Doctrine\DBAL\Platforms\PostgreSQL100Platform, (methods getListSequencesSQL(), getListNamespacesSQL(), and getListTablesSQL())
• Doctrine\DBAL\Platfoms\PostgreSqlPlatform, (method getTableWhereClause())
• and Doctrine\DBAL\Schema\PostgreSqlSchemaManager, (method getSchemaNames()),

Since, DBAL can connect to my Psql Instance.

[dextervip]

@Knudian, DBAL fails connecting to timescaledb 11. Is that the easiest solution you have found?

[Knudian]

@dextervip As far as I know, it still work (I did this tweak for the office I was at that time).

I have a private project using TimescaleDB, I will look if there is any update to do.

[dextervip]

@Knudian I just got it working by overriding platform sevice. I will let a gist in case of someone needs it. https://gist.github.com/dextervip/a2f384050748d6ee3ed7d573425e9d58