Keg-only PostgreSQL is incompatible with extensions like PostGIS

9999years commented 3 months ago

Summary

The postgresql@16 formula looks for extension files in directories like /opt/homebrew/opt/postgresql@16/lib. Extensions like PostGIS cannot install files into these directories (correctly) due to the Homebrew sandbox. As a result, keg-only PostgreSQL formulae like postgresql@16 are inherently incompatible with extensions.

This is a blocker for upgrading the default PostgreSQL version (as suggested in discussions like #5244), and has not been identified as an issue in discussions on workarounds for installing PostGIS for alternate PostgreSQL versions (like #3987).

### `brew gist-logs ` link OR `brew config` AND `brew doctor` output

`brew config`: ``` HOMEBREW_VERSION: 4.3.12-62-g870b342 ORIGIN: https://github.com/Homebrew/brew HEAD: 870b3423bc1f49205536316a1be25e268a653a8b Last commit: 2 hours ago Core tap HEAD: 3e9955380ed95685903c38919367b7a1d4d84de6 Core tap last commit: 4 days ago Core tap JSON: 02 Aug 16:38 UTC Core cask tap HEAD: 09c42760175cc88bc0c1fb2ace3e82521ca5df4f Core cask tap last commit: 4 days ago Core cask tap JSON: 02 Aug 16:38 UTC HOMEBREW_PREFIX: /opt/homebrew HOMEBREW_CASK_OPTS: [] HOMEBREW_EDITOR: nvim HOMEBREW_MAKE_JOBS: 20 HOMEBREW_SORBET_RUNTIME: set Homebrew Ruby: 3.3.4 => /opt/homebrew/Library/Homebrew/vendor/portable-ruby/3.3.4/bin/ruby CPU: 20-core 64-bit arm_firestorm_icestorm Clang: 15.0.0 build 1500 Git: 2.45.2 => /opt/homebrew/bin/git Curl: 8.6.0 => /usr/bin/curl macOS: 14.5-arm64 CLT: 15.3.0.0.1.1708646388 Xcode: N/A Rosetta 2: false ``` `brew doctor`: ``` Your system is ready to brew. ```

Verification

[X] My brew doctor output says Your system is ready to brew. and am still able to reproduce my issue.
[X] I ran brew update and am still able to reproduce my issue.
[X] I have resolved all warnings from brew doctor and that did not fix my problem.
[X] I searched for recent similar issues at https://github.com/Homebrew/homebrew-core/issues?q=is%3Aissue and found no duplicates.

What were you trying to do (and why)?

I want to use a version of PostgreSQL other than PostgreSQL 14 with extensions such as PostGIS.

What happened (include all command output)?

PostgreSQL looks in several directories for extensions, including a --libdir (for .a files), --pkglibdir (for .dylib files), and --sharedir (for .sql and .control) files.

In the postgresql@14 formula (which is not keg-only), these directories are under $HOMEBREW_PREFIX:

$ pg_config --libdir
/opt/homebrew/lib/postgresql@14
$ pg_config --pkglibdir
/opt/homebrew/lib/postgresql@14
$ pg_config --sharedir
/opt/homebrew/share/postgresql@14

This means that when postgis is installed and linked into $HOMEBREW_PREFIX, files like /opt/homebrew/opt/postgis/share/postgresql@14/extension/postgis_topology--3.4.2.sql are visible at paths like /opt/homebrew/share/postgresql@14/extension/postgis_topology--3.4.2.sql.

Newer PostgreSQL formulae like postgresql@16 are keg-only, so they look for extensions within their opt prefix:

$ /opt/homebrew/opt/postgresql@16/bin/pg_config --libdir
/opt/homebrew/opt/postgresql@16/lib
$ /opt/homebrew/opt/postgresql@16/bin/pg_config --pkglibdir
/opt/homebrew/opt/postgresql@16/lib/postgresql
$ /opt/homebrew/opt/postgresql@16/bin/pg_config --sharedir
/opt/homebrew/opt/postgresql@16/share/postgresql@16

As a result, it is impossible to use any extensions with the postgresql@16 formula as designed, because if (e.g.) the postgis formula tries to install files into /opt/homebrew/opt/postgresql@16/lib the Homebrew sandbox will rightfully stop it. And because PostgreSQL cannot look in multiple directories for extensions, there appears to be no easy way around this.

What did you expect to happen?

I expected postgresql@16 to locate the postgis extension I installed.

Step-by-step reproduction instructions (by running `brew` commands)

Here we install [the `postgis@3.4.2` formula I've created](https://github.com/9999years/homebrew-tap-bad-postgis/blob/21111035da0ecd0a1a1c90107349b632669fcfbf/Formula/postgis@3.4.2.rb) with `brew extract` as [suggested by maintainers on the GitHub discussions](https://github.com/orgs/Homebrew/discussions/3987#discussioncomment-4341618). I've replaced the `postgresql@14` dependency with `postgresql@16`, and `postgresql@16` cannot find the newly-installed PostGIS:

brew tap 9999years/homebrew-tap-bad-postgis
brew install postgresql@16 9999years/homebrew-tap-bad-postgis/postgis@3.4.2
# You may need to stop any other running PostgreSQL servers.
brew services start postgresql@16
/opt/homebrew/opt/postgresql@16/bin/psql postgres -c "SELECT name, default_version FROM pg_available_extensions WHERE name LIKE '%postgis%'"
#  name | default_version
# ------+-----------------
# (0 rows)

Meanwhile, postgresql@14 isn't keg-only and as a result can find postgis:

brew install postgresql@14 postgis
brew services start postgresql@14
psql postgres -c "SELECT name, default_version FROM pg_available_extensions WHERE name LIKE '%postgis%'"
#           name          | default_version
# ------------------------+-----------------
#  postgis                | 3.4.2
#  postgis_tiger_geocoder | 3.4.2
#  postgis_topology       | 3.4.2
#  postgis_raster         | 3.4.2
#  postgis_sfcgal         | 3.4.2
# (5 rows)

Note that the installation directories don't quite line up; the postgis@3.4.2 formula I've created gets installed to /opt/homebrew/lib/postgresql@16 and /opt/homebrew/share/postgresql@16, but the postgresql@16 formula (if it were linked into the $HOMEBREW_PREFIX, which it is not) would be looking in /opt/homebrew/lib, /opt/homebrew/lib/postgresql, and /opt/homebrew/share/postgresql@16 instead. However, this is irrelevant because postgresql@16 isn't looking for extensions anywhere outside of its opt prefix regardless.

9999years commented 3 months ago

I think that the path forward may be to make PostgreSQL formulae not keg-only but also to include versioned executables (like psql@16) to avoid link conflicts and make it possible to upgrade from one PostgreSQL version to another without using brew link --overwrite or similar techniques. However, this is a breaking change and requires commitment, so I wanted to open an issue here for discussion first.

carlocab commented 3 months ago

There's ongoing work on this at #167634 -- it sounds similar to the approach you're proposing.

cho-m commented 3 months ago

I think that the path forward may be to make PostgreSQL formulae not keg-only but also to include versioned executables (like psql@16)

I've tried this before but it impacts too much existing tooling/users.

In current state, removing keg_only is impossible as a large enough number of users want the official commands on their PATH (currently possible after brew link and some external projects expect this to work). This means all postgresql@* formulae must install unversioned commands into bin and thus conflict. Having hard conflicting postgresql@* formulae then makes DB migrations difficult so most need to be keg_only.

Only way forward I see for non-keg_only would be implementing a "soft" conflict (https://github.com/Homebrew/brew/issues/16398).

The goal of #167634 is to hack around the user voiced requirements like

Official/Unversioned command must be available in #{bin} for every postgresql@* formula.
If keg_only, brew link postgresql@X must expose the official binaries on default PATH along with libs/headers in locations that are non-breaking to existing usage.

Anyways, my plan is to try to introduce some modifications like #167634 with PostgreSQL 17.

github-actions[bot] commented 2 months ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.

9999years commented 2 months ago

Ohhhh that's how the issue tracker is so spartan. Feels super disrespectful — not every open issue will have activity every three weeks! It also encourages pointless noise like this comment in the issue tracker, making issues harder to read unless they're so trivial they can be closed in a few weeks.

gromgit commented 2 months ago

Feels super disrespectful — not every open issue will have activity every three weeks!

Auto-marking issues as "stale" after three weeks does several things:

prunes issues that have been dealt with but weren't closed for whatever reason
prunes issues that no one is pursuing any more, not even the original poster--you'd be surprised how many folks just walk away when they've found an alternative solution
reminds everyone already involved in an issue to take a second look--it got you back, didn't it?
speaks the Truth: the issue has gone stale, and should either be updated or left to die

Bottom line: Homebrew maintainers are super-busy folks, and Homebrew isn't even their day job. Getting an automated mechanism to prune issues that have been abandoned, and remind everyone of what remains, seems like a wise thing to do.

It also encourages pointless noise like this comment in the issue tracker, making issues harder to read unless they're so trivial they can be closed in a few weeks.

Not really. Truly pointless noise can always be collapsed by maintainers if they so desire, and I'm guessing they will if an issue thread devolves into a series of "bumps".

github-actions[bot] commented 1 month ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.

Homebrew / homebrew-core