Feature Request: support for postgres extensions

[kiwicopple]

Source: https://news.ycombinator.com/item?id=37020804

This is very cool! Does it also support extensions? For example, would it be aware of additional functions and handling of geometries if PostGIS is installed?

[kkostov]

This is an excellent suggestion! Getting auto-complete for functions provided by an extension would be a great first step. Perhaps also adding information about the expected signature and return data types.

I'm not sure how the lsp works, but one can imagine tackling some more challenging issues like for example get a warning if one is trying to compare geometries in different, incompatible projections (in case of the PostGIS example, but probably applicable for other extensions).

[benjamin-kirkbride]

I came here to point out that (IMO) nailing extensions will be what determines if this project is "good" or "great".

Most projects in this space do not consider extensions from the beginning, and retrofitting to support them (in a way that doesn't require huge breaking changes) is almost impossible.

Complications that extensions can cause:

• new "objects" (functions, data types, indexes, tables, etc)
• new syntax (!!)
• extension dependencies
• versioning (difference between extension versions)

Now depending on the scope of postgres_lsp not all of these things may come into play, almost certainly some of them will, and my (not so) secret hope for this project is that it provides a extensible library with a universal data model that can be used for all sorts of postgres tooling (not just LSP), hence bringing up everything I can think of.

Two ways I can see that extensions could be supported (nomenclature WIP):

Reflection / Introspection

Truly install/source the extension, and "process" / "install" it in the way that Postgres does, then inspect what it "does" and inject that into the postgres_lsp datamodel.

Pros

• (in theory) support every conceivable extension (and version of that extension)
• (in theory) support every combination of extensions (extensions can mutate other extensions)

Cons

• this would probably be really hard to do (is there an equivalent of libpg_query for extensions?)
• not batteries included for users
  • this would require users to somehow source the extensions they intend to use and "inject" them into postgres_lsp (pgxn could help UX wise with this?)
  • alternatively, you could have users point to an existing pg db and "reflect" from it. Problems with this approach:
    • It is sometimes very hard (impossible?) to determine what extension is responsible for a specific object. See: Error Using dbtoyaml With Supabase Schema perseas/Pyrseas#244 (comment)
    • Users may not want to have to have a running database as a requirement to use postgres_lsp

Configuration / Stubs

Support some sort of static declarative config to describe what a given version of an extension will do.

Pros

• likely much much easier to implement initially
• likely can be done in a more piecemeal fashion (we just support new index extensions for now, for instance)

Cons

• users will need to somehow communicate to postgres_lsp what extensions (with versions) they are using
  • there is no requirements.txt, package-lock.json or equivalent to piggyback off of here. Looking for CREATE EXTENSION doesn't really work because most sql files you are working with will not contain them. Searching the entire project for CREATE EXTENSION is likely also a bad idea.
  • presumably there will be some sort of a postgres_lsp.conf (if only Proposal: "toolconfig" files for use cases beyond .editorconfig files editorconfig/editorconfig#482 was done 😢) so this would likely go in there.
• covering all extensions (that anyone cares about, even) will be extremely hard
  • in theory if/when this project gets big extension authors and prolific users (such as yours truly 😉) will likely start providing/updating their own postgres_lsp stub files, which would certainly help
• by the time we have established a complete format for defining what an extension does, have we basically built our (worse) Postgres extension format and have a worse version of the "Reflection / Introspection" idea?