Handle extensions extensibly

[paupino]

I think we're going to have to really think about how to implement these properly. They can create their own types/schemas/tables/functions etc which is useful information for post compilation steps. e.g. we may want to error when we find a dependency that is unrecognized, however at the moment things like ST_POINT which come as part of postgis aren't able to be accurately captured.

Perhaps we create explicit psqlpack packages which can be linked? If we do that, how do we maintain them? Who maintains them?

[paupino]

Consider generating the extensions by talking to the database directly (i.e. pg_extension -> pg_depend). These could be locally cached by version etc.

[paupino]

This would also solidify that extensions should not be managed, or rather deployed, via psqlpack. This means that during publish we validate that the appropriate extension (and version) is installed and do not try to install the extension ourselves (preferred due to binary dependencies etc).

[paupino]

It may make sense rather than trying to define the extension in detail as per a standard psqlpack to just have a psqldef or psqlext object that defines the definitions - similar to header files in C. Alternatively, as mentioned, we could try do this dynamically on the database however this prevents us doing disconnected checks so is less appealing (except for generating the definitions in the first place).

I'm going to remove the nicetohave label as I believe this is necessary in order to sensibly work with extensions.

[paupino]

It is feasible that we also want to include extension versions with our layout. This allows us to run extension upgrades automatically as necessary after querying pg_extension. i.e. SELECT oid, extname, extversion FROM pg_extension WHERE extname=$1

[paupino]

We could simply replace this with SELECT * FROM pg_available_extension_versions WHERE name=$1 as that will define what is available vs what is installed.

[paupino]

We should be able to generate an extension's definitions by essentially taking a dump of something similar to:

SELECT c.relname, COALESCE(proc.proname || '(' || oidvectortypes(proc.proargtypes) || ')' ,typ.typname, cd.relname, op.oprname,
                    cs.typname || ' AS ' || ct.typname || ') ', opcname, opfname) "name"
FROM pg_depend d 
INNER JOIN pg_extension e ON d.refobjid = e.oid 
INNER JOIN pg_class c ON c.oid = d.classid
LEFT JOIN pg_proc proc ON proc.oid = d.objid
LEFT JOIN pg_type typ ON typ.oid = d.objid
LEFT JOIN pg_class cd ON cd.oid = d.objid
LEFT JOIN pg_operator op ON op.oid = d.objid
LEFT JOIN pg_cast ca ON ca.oid = d.objid
LEFT JOIN pg_type cs ON ca.castsource = cs.oid
LEFT JOIN pg_type ct ON ca.casttarget = ct.oid
LEFT JOIN pg_opclass oc ON oc.oid = d.objid
LEFT JOIN pg_opfamily ofa ON ofa.oid = d.objid
WHERE d.deptype = 'e' and e.extname = 'postgis'
ORDER BY c.relname, "name"

[paupino]

I'm going to break this into various steps:

• [x] Update the extensions property in the project file to optionally specify version.
• [x] Add the ability to install/update an extension.
• [x] Create a command to extract an extension's definitions from a database and store in JSON.
• [x] Add the ability for extension definitions to be loaded and referenced during type checking etc. This may require some minor refactoring of the parsing logic.
• [x] Add the ability to specify search paths for extension definitions.
• [ ] With the absence of search paths, default to a "cache" directory and/or download from the definition from a server.

[paupino]

131 now replaces the rest of this ticket.