postgresql_12: python support missing

[W1M0R]

Describe the bug Postgres already exists as a nix package (https://github.com/NixOS/nixpkgs/blob/39cce06a6bd8fb70a5bf5d64d2aaea954dab44b8/pkgs/servers/sql/postgresql/default.nix#L56) bundled with some packages (https://github.com/NixOS/nixpkgs/blob/master/pkgs/servers/sql/postgresql/packages.nix).

Postgres itself has built-in support for Python, but it needs to be enabled during the build process (https://github.com/postgres/postgres/blob/7136bf34f28892362144ae2e350714836a5c0c0c/configure#L1558) via the --with-python configure flag.

The current implementation of postgres in nix does not enable python support, and it also doesn't seem to perform any detection whether python support should be offered. There is already some form of conditional feature support (for example via the icuEnabled attribute. It could be possible to add a pythonEnabled attribute that enables python. My knowledge of nix is clearly lacking in this regard, but I hope the idea makes sense.

To Reproduce Steps to reproduce the behavior:

1. Install postgresql_12 via nix-shell then use pg_ctl to initialize and start a server instance
2. Use psql to enter the postgres instance
3. Execute 'create extension plpython3u;'

Expected behavior The ability to enable the plpython3u extension.

Screenshots

This is a screenshot of the current configureFlags of postgres (https://github.com/NixOS/nixpkgs/blob/39cce06a6bd8fb70a5bf5d64d2aaea954dab44b8/pkgs/servers/sql/postgresql/default.nix#L56). Somehow it should be possible to get --with-python into this list.

Additional context

I am trying to get a nix-shell working that has a postgresql_12 with python support. I am not trying to get a working configuration in NixOS.

It should be possible to use packageOverrides to explicitly add support for plpython. I am still exploring this possibility. Others have done something similar for other postgres extensions (https://github.com/NixOS/nixpkgs/issues/38369). There is a nix pill that describes how to override packages here (https://nixos.org/guides/nix-pills/override-design-pattern.html#idm140737319871088) and here (https://nixos.org/guides/nix-pills/nixpkgs-overriding-packages.html). However, it looks like this can get complicated (https://stackoverflow.com/questions/41980840/how-to-customize-a-nixpkgs-package-configureflags). More examples of overriding can be found here: http://www.bnikolic.co.uk/nix-cheatsheet.html#orgb5bd923. Another approach for overriding packages is via overlays (https://nixos.wiki/wiki/Overlays). By including support for python in the derivation itself, it may be easier for new users to get started.

@bbigras attempted to add support for multicorn (https://github.com/NixOS/nixpkgs/pull/91510/files), which is implemented using python (https://multicorn.org/), but this doesn't use plpython3u, and is thus not directly related to this issue. Just mentioning it here for others that are trying to figure out how to get python in their database backend.

The author of the following article shows how to configure postgres using withPackages: https://wilspi.com/post/tech/nix-recipes/postgresql/:

let
 postgresql_12 = pkgs.postgresql_12.withPackages (p: [ p.postgis ]);

I can imagine a solution that may look like this: pkgs.postgresql_12.withPackages (p: [ p.python3 ]).

Another possibility, is to have something like postgresql_12Full (see python38Full etc.).

Notify maintainers

@marsam Can you recommend a way to get plpython3u enabled in the nix build of postgres?

Metadata Please run nix-shell -p nix-info --run "nix-info -m" and paste the result.

nix-shell: 
 - system: `"x86_64-linux"`
 - host os: `Linux 5.4.0-31-generic, Ubuntu, 20.04 LTS (Focal Fossa)`
 - multi-user?: `no`
 - sandbox: `yes`
 - version: `nix-env (Nix) 2.3.10`
 - channels(vagrant): `"nixpkgs-21.05pre282880.5268ee2ebac"`
 - nixpkgs: `/home/vagrant/.nix-defexpr/channels/nixpkgs`

Maintainer information:

# a list of nixpkgs attributes affected by the problem
attribute:
# a list of nixos modules affected by the problem
module:

[W1M0R]

The following extract seems to work so far:

...
let
  # https://github.com/NixOS/nixpkgs/blob/master/pkgs/servers/x11/xorg/overrides.nix
  postgresql_12_plpython38 = pkgs.postgresql_12.overrideAttrs (attrs: {
    buildInputs = attrs.buildInputs or [] ++ [ pkgs.python38 ];
    configureFlags = attrs.configureFlags or [] ++ [ "--with-python" ];
    # https://nixos.org/guides/nix-pills/basic-dependencies-and-hooks.html#idm140737319490896
    propagatedBuildInputs = attrs.propagatedBuildInputs or [] ++ [ pkgs.python38 ];
  });
...

[stale[bot]]

I marked this as stale due to inactivity. → More info

[addict3d]

I'm interested in using this but not sure when I'd be able to pick it up.