postgresql@14: rename from postgresql, make versioned.

[MikeMcQuaid]

PostgreSQL makes more sense to only be a versioned formula. It breaks on every major version upgrade until people run a tool we provide for them which is not 100% bug-free.

While we're here, fix up the existing versioned postgresql formulae a bit.

Companion PR: https://github.com/Homebrew/brew/pull/13676

[Bo98]

For dependents: we should see what can use libpq instead.

[MikeMcQuaid]

@Bo98 Agreed but I don't think that has to be in this PR as it blows up the scope a decent amount.

[SMillerDev]

Most will also still link to the full formula if you modify it in this PR.

[MikeMcQuaid]

Most will also still link to the full formula if you modify it in this PR.

@SMillerDev Sorry, you've lost me a bit, can you elaborate?

[SMillerDev]

In my experience if you would do what Bo suggests you just end up with a lot of undeclared linkage errors so better to do it in a PR where postgresql isn't modified.

[bayandin]

Also should be updated here https://github.com/Homebrew/homebrew-core/blob/76e20f3c5a4b0419567cfab915be98666bddf78b/synced_versions_formulae.json#L37

and there https://github.com/Homebrew/homebrew-core/blob/76e20f3c5a4b0419567cfab915be98666bddf78b/Formula/libpq.rb#L8-L10

I'd drop libpq and postgresql from synced_versions_formulae.json and copied livecheck from postgresql to libpq

[bayandin]

I've creates a PR with swapping postgresql dependency with libpq: https://github.com/Homebrew/homebrew-core/pull/107797

UPD: Merged!

[carlocab]

On ARM:

Error: 8 failed steps!
brew audit postgresql@14 --new-formula
brew install --verbose --build-bottle citus
brew install --verbose --build-bottle pg_cron
brew install --verbose --build-bottle pg_partman
brew install --verbose --build-bottle pgroonga
brew install --verbose --build-bottle postgis
brew install --verbose --build-bottle temporal_tables
brew install --verbose --build-bottle wal2json

[carlocab]

Will need a rebase after #108093. Also needs a rebase for postgraphile.

[MikeMcQuaid]

Thanks @carlocab. Feel free to jump in and beat me to it/finish it off if desired as I won't be super active here.

[MikeMcQuaid]

@carlocab @bayandin @Bo98 any chance of an assist here? In (almost) all cases seems to be missing a postgres.h which I presume would ideally be from libpq anyway.

[bayandin]

@carlocab @bayandin @Bo98 any chance of an assist here? In (almost) all cases seems to be missing a postgres.h which I presume would ideally be from libpq anyway.

I'll have a look today

[carlocab]

For citus, the failing command seems to be https://github.com/Homebrew/homebrew-core/runs/7910643501?check_suite_focus=true#step:6:151 with the error

  /private/tmp/citus-20220818-62641-le6qvu/citus-11.0.5//src/bin/pg_send_cancellation/pg_send_cancellation.c:31:10: fatal error: 'postgres_fe.h' file not found
  #include "postgres_fe.h"
           ^~~~~~~~~~~~~~~

This header can be found at include/postgresql/server. (There's also one at include/postgresql/internal, but maybe we don't want to use that one?) The problem is that the build is including the following directories from postegresql@14 instead:

• /opt/homebrew/opt/postgresql@14/include
• /opt/homebrew/opt/postgresql@14/include/server
• /opt/homebrew/opt/postgresql@14/include/internal

Note the missing intermediate postgresql directory.

This might be a bug in the way pg_config is configured.

[carlocab]

Unfortunately, pg_config is a binary, so I'll need to poke at the source. However, I could find these paths baked into it:

7a83 /usr/local/opt/postgresql@14/share/postgresql@14
7ab4 /usr/local/Cellar/postgresql@14/14.5_1/bin
7adf /usr/local/etc/postgresql
7af9 /usr/local/opt/postgresql@14/include
7b1e /usr/local/opt/postgresql@14/include/server
7b4a /usr/local/opt/postgresql@14/lib
7b6b /usr/local/Cellar/postgresql@14/14.5_1/lib/postgresql
7ba1 /usr/local/Cellar/postgresql@14/14.5_1/share/locale
7bd5 /usr/local/Cellar/postgresql@14/14.5_1/share/doc/postgresql@14
7c14 /usr/local/Cellar/postgresql@14/14.5_1/share/man

What I'm confused about is that the layout of the postgresql keg did not change when we made it versioned. We still needed that intervening postgreqsl directory before, so I don't know why it's gone missing from include paths now.

Here's the equivalent to the paths above from our existing unversioned postgresql:

7ad9 /usr/local/share/postgresql
7af5 /usr/local/Cellar/postgresql/14.5/bin
7b1b /usr/local/etc/postgresql
7b35 /usr/local/include
7b48 /usr/local/include/postgresql
7b66 /usr/local/include/postgresql/server
7b8b /usr/local/lib
7b9a /usr/local/lib/postgresql
7bb4 /usr/local/Cellar/postgresql/14.5/share/locale
7be3 /usr/local/Cellar/postgresql/14.5/share/doc/postgresql
7c1a /usr/local/Cellar/postgresql/14.5/share/man

This time, the include paths seem right?

[carlocab]

Might be related to this:

https://github.com/postgres/postgres/blob/c9818798147a4eec00bba61a05fa9bc88398ec3b/src/Makefile.global.in#L82-L86

https://github.com/postgres/postgres/blob/c9818798147a4eec00bba61a05fa9bc88398ec3b/src/Makefile.global.in#L128-L131

[MikeMcQuaid]

@carlocab Perhaps this only works when it is linked?

[carlocab]

Perhaps this only works when it is linked?

That too, but not exactly. There's some weird Makefile logic that scans the prefix for the string postgresql and inserts postgresql there if it's not there.

It worked for unversioned postgresql because we configured it as if it was being installed into HOMEBREW_PREFIX:

https://github.com/Homebrew/homebrew-core/blob/9292f0be26a8e19b73103075f6acfaf90e278485/Formula/postgresql.rb#L51-L53

Now it's getting messed up by seeing postgresql in the opt_* paths.

[bayandin]

I think I made pg_config work with this patch:

--- a/Formula/[email protected]
+++ b/Formula/[email protected]
@@ -77,7 +77,13 @@ class PostgresqlAT14 < Formula
     # in ./configure, but needs to be set here otherwise install prefixes containing
     # the string "postgres" will get an incorrect pkglibdir.
     # See https://github.com/Homebrew/homebrew-core/issues/62930#issuecomment-709411789
-    system "make", "pkglibdir=#{lib}/postgresql"
+    system "make", "datadir=#{opt_pkgshare}",
+                   "libdir=#{opt_lib}",
+                   "pkglibdir=#{opt_lib}/postgresql",
+                   "includedir=#{opt_include}",
+                   "pkgincludedir=#{opt_include}/postgresql",
+                   "includedir_server=#{opt_include}/postgresql/server",
+                   "includedir_internal=#{opt_include}/postgresql/internal"
     system "make", "install-world", "datadir=#{pkgshare}",
                                     "libdir=#{lib}",
                                     "pkglibdir=#{lib}/postgresql",

[carlocab]

That needs to be backported to the earlier versions, as they'll have broken pg_config binaries too.

[MikeMcQuaid]

Although, as noted by @bo98 elsewhere, anything that can be made to use libpq instead probably should be.

[bayandin]

Although, as noted by @Bo98 elsewhere, anything that can be made to use libpq instead probably should be.

libpq only is not enough for building Postgres extensions; I think I've moved everything to it in https://github.com/Homebrew/homebrew-core/pull/107797 (but I could have miss something)

[MikeMcQuaid]

@bayandin thanks ❤️

[bayandin]

Regarding extensions, I'm not sure what is the best way to approach it. The problem is the most common way to install extensions is to put extensions into Postgres dirs HOMEBREW_PREFIX/lib/postgresql and HOMEBREW_PREFIX/share/postgresql/extenstions. With making postgresql keg_only, it's impossible to write into other formula directories. I'm thinking about making postgresql versioned, without making it keg-only. For this, we'll need to rename binaries; I guess adding a versioned suffix should be enough (like psql-14), and put libraries (and includes) into a versioned directory (like postgresql@14). What do you think?

[MikeMcQuaid]

@bayandin Do we need to rename binaries if it's versioned and all the others versions are keg-only?

[Bo98]

What is the ABI compatibility of PostgreSQL extensions like? Ideally brew install postgis would not depend on a particular version of PostgreSQL and install something that can be used by all versions.

The problem is the most common way to install extensions is to put extensions into Postgres dirs

Could we not just give it a directory to use in HOMEBREW_PREFIX or does PostgreSQL itself install extensions?

I'm thinking something like how we did Python's site-packages.

[bayandin]

@bayandin Do we need to rename binaries if it's versioned and all the others versions are keg-only?

In this case, we don't need to rename binaries. but we will need to do it when PostgreSQL is released (should be in late 2022).

Also, I think it will be useful to build extensions for several Postgres versions, which should be totally possible with what I proposed above.

[MikeMcQuaid]

but we will need to do it when PostgreSQL is released (should be in late 2022).

typo?

Could we just keep this keg-only until we rebuild all extensions on a newer version?

[bayandin]

What is the ABI compatibility of PostgreSQL extensions like? Ideally brew install postgis would not depend on a particular version of PostgreSQL and install something that can be used by all versions.

In general, extensions aren't compatible (between 2 major releases).

Could we not just give it a directory to use in HOMEBREW_PREFIX or does PostgreSQL itself install extensions?

Postgres loads extensions from pg_config --sharedir/extenstions (SQL files) and from pg_config --pkglibdir (libraries). Maybe it's possible to configure it somehow in postgresql.conf, but I didn't find an easy way to do it (didn't look good enough tho)

[bayandin]

but we will need to do it when PostgreSQL is released (should be in late 2022).

typo?

Ah, right, I meant the next version of PostgreSQL — 15

Could we just keep this keg-only until we rebuild all extensions on a newer version?

Sure, we can do it until we come up with a better idea

[Bo98]

Could we just keep this keg-only until we rebuild all extensions on a newer version?

Sure, we can do it until we come up with a better idea

As long as it's not a major delay, I would suggest any major changes are done now and not later so they can be a part of the 3.6.0 communication.

If, for example, we took the versioned approach (e.g. include/postgresql/14, setting --bindir=libexec/bin and symlinking versioned binaries) then this would be a notable behavioural change that may require changes to a user's existing workflow (e.g. running psql alone might stop working) and is worth communicating.