sql: fully support CREATE EXTENSION syntax

[otan]

Note this is separate from implementing individual extensions.

Today, we already implement some extensions in the database itself, e.g. PostGIS. However, we still differ from PG in that we don't put things in the appropriate schema, so we have hacks to make, e.g. public.geometry_columns accessible.

We should support the CREATE EXTENSION WITH options and the like. Probably worth aliasing all of PostGIS's UDFs to the public schema as well for similarity.

This also means support for DROP EXTENSION / COMMENT ON EXTENSION

Jira issue: CRDB-12255

[LiZhang19817]

@otan thanks for the updates, so if we want to test our application with cockraochdb(postgres), and we need to install "CREATE EXTENSION pg_trgm", currently which deployment type support that.

[otan]

i believe they all do provided they are on v23.1+

[LiZhang19817]

@otan any idea why after I executed install the extension without error message, but found the extension was not installed.

psql --host=quay390-5554.8nk.cockroachlabs.cloud --port=26257 --username luffy --password --dbname quay
Password: 
psql (15.3, server 13.0.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, compression: off)
Type "help" for help.

quay=> create extension pg_trgm;
CREATE EXTENSION
quay=> \dx
     List of installed extensions
 Name | Version | Schema | Description 
------+---------+--------+-------------
(0 rows)

quay=> create extension pg_trgm;
CREATE EXTENSION
quay=> \dx
     List of installed extensions
 Name | Version | Schema | Description 
------+---------+--------+-------------
(0 rows)

quay=> 

[otan]

we don't populate that table, but the functionality exists cc @rafiss

[rafiss]

https://github.com/cockroachdb/cockroach/issues/80012 is a more specific issue that would make \dx start working.