[meta-oe][PATCH 0/3] postgresql: add PGXS extension support and pg_cron

[meta-oe][PATCH 0/3] postgresql: add PGXS extension support and pg_cron

[Babanpreet Singh]

PGXS extensions cannot be cross-built in OE today. Patch 1 makes the
postgresql recipe support that, patch 2 adds a class for extension
recipes and patch 3 adds pg_cron as the first one.

Built for qemux86-64 and qemuarm64 and run in a qemux86-64 image. I
also built pgvector, pgaudit, pg_partman, orafce and hll through the
class to check it is not specific to pg_cron.

Babanpreet Singh (3):
  postgresql: make PGXS usable for cross-building extensions
  pgxs.bbclass: add class for PostgreSQL extensions
  pg-cron: add recipe

 meta-oe/classes/pgxs.bbclass                  | 22 +++++++
 .../packagegroups/packagegroup-meta-oe.bb     |  1 +
 meta-oe/recipes-dbs/pg-cron/pg-cron_1.6.8.bb  | 13 ++++
 .../recipes-dbs/postgresql/files/pg_config    | 43 +++++++++++++
 meta-oe/recipes-dbs/postgresql/postgresql.inc | 62 +++++++++++++++++--
 5 files changed, 135 insertions(+), 6 deletions(-)
 create mode 100644 meta-oe/classes/pgxs.bbclass
 create mode 100644 meta-oe/recipes-dbs/pg-cron/pg-cron_1.6.8.bb
 create mode 100755 meta-oe/recipes-dbs/postgresql/files/pg_config

-- 
2.43.0

[meta-oe][PATCH 1/3] postgresql: make PGXS usable for cross-building extensions

[Babanpreet Singh]

Extensions built with PGXS need pg_config and Makefile.global, but
pg_config is a target binary and the staged Makefile.global has its
paths stripped.

Stage a shell pg_config in crossscripts and fix up the staged PGXS
makefiles so extensions can be built against the sysroot.

Signed-off-by: Babanpreet Singh <bbnpreetsingh@gmail.com>
---
 .../recipes-dbs/postgresql/files/pg_config    | 43 +++++++++++++
 meta-oe/recipes-dbs/postgresql/postgresql.inc | 62 +++++++++++++++++--
 2 files changed, 99 insertions(+), 6 deletions(-)
 create mode 100755 meta-oe/recipes-dbs/postgresql/files/pg_config

diff --git a/meta-oe/recipes-dbs/postgresql/files/pg_config b/meta-oe/recipes-dbs/postgresql/files/pg_config
new file mode 100755
index 0000000000..1fab031a90
--- /dev/null
+++ b/meta-oe/recipes-dbs/postgresql/files/pg_config
@@ -0,0 +1,43 @@
+#!/bin/sh
+# pg_config for cross builds, the real one is a target binary.
+# Build paths are answered inside the sysroot, install paths as on the target.
+
+sysroot=$(cd "$(dirname "$0")" && pwd -P)
+sysroot=${sysroot%@CROSSSCRIPTS@}
+
+value() {
+	case $1 in
+	--bindir) echo "@BINDIR@" ;;
+	--docdir) echo "@DOCDIR@" ;;
+	--htmldir) echo "@DOCDIR@" ;;
+	--includedir) echo "$sysroot@INCLUDEDIR@" ;;
+	--pkgincludedir) echo "$sysroot@PKGINCLUDEDIR@" ;;
+	--includedir-server) echo "$sysroot@PKGINCLUDEDIR@/server" ;;
+	--libdir) echo "$sysroot@LIBDIR@" ;;
+	--pkglibdir) echo "@PKGLIBDIR@" ;;
+	--localedir) echo "@LOCALEDIR@" ;;
+	--mandir) echo "@MANDIR@" ;;
+	--sharedir) echo "@SHAREDIR@" ;;
+	--sysconfdir) echo "@SYSCONFDIR@" ;;
+	--pgxs) echo "$sysroot@PKGLIBDIR@/pgxs/src/makefiles/pgxs.mk" ;;
+	--version) echo "PostgreSQL @VERSION@" ;;
+	*)
+		echo "pg_config: $1 is not available in a cross build" >&2
+		exit 1
+		;;
+	esac
+}
+
+all="bindir docdir htmldir includedir pkgincludedir includedir-server \
+	libdir pkglibdir localedir mandir sharedir sysconfdir pgxs version"
+
+if [ $# -eq 0 ]; then
+	for opt in $all; do
+		printf '%s = %s\n' "$(echo "$opt" | tr a-z A-Z)" "$(value "--$opt")"
+	done
+	exit 0
+fi
+
+for arg; do
+	value "$arg"
+done
diff --git a/meta-oe/recipes-dbs/postgresql/postgresql.inc b/meta-oe/recipes-dbs/postgresql/postgresql.inc
index 76e90df765..5ac0513085 100644
--- a/meta-oe/recipes-dbs/postgresql/postgresql.inc
+++ b/meta-oe/recipes-dbs/postgresql/postgresql.inc
@@ -30,6 +30,7 @@ SRC_URI = "https://ftp.postgresql.org/pub/source/v${PV}/${BP}.tar.bz2 \
     file://postgresql-setup \
     file://postgresql.service \
     file://run-ptest \
+    file://pg_config \
 "
 
 LEAD_SONAME = "libpq.so"
@@ -284,16 +285,65 @@ do_install:append() {
     install -m 0644 ${UNPACKDIR}/postgresql.service ${D}${systemd_unitdir}/system
     sed -i -e 's,@BINDIR@,${bindir},g' \
         ${D}${systemd_unitdir}/system/postgresql.service
-    # Remove the build path
+    # Make the installed PGXS makefiles usable from extension recipes: take
+    # the toolchain from the environment and drop the build paths. PostgreSQL
+    # 18 skips pg_config when prefix is set, the first expression undoes that.
     if [ -f ${D}${libdir}/${BPN}/pgxs/src/Makefile.global ]; then
-        sed -i -e 's#${RECIPE_SYSROOT}##g' \
-               -e 's#${RECIPE_SYSROOT_NATIVE}##g' \
-               -e 's#${WORKDIR}##g' \
-               -e 's#${TMPDIR}##g' \
-             ${D}${libdir}/${BPN}/pgxs/src/Makefile.global
+        sed -i \
+            -e 's#^ifneq (,$(if $(PGXS),,1)$(prefix))$#ifndef PGXS#' \
+            -e 's#^CC = .*#CC := $(CC)#' \
+            -e 's#^CXX = .*#CXX := $(CXX)#' \
+            -e 's#^CPP = .*#CPP := $(CPP)#' \
+            -e 's#^LD = .*#LD := $(LD)#' \
+            -e 's# -fcanon-prefix-map##g' \
+            -e 's# -f\(file\|debug\|macro\)-prefix-map=[^ ]*##g' \
+            -e 's#^CFLAGS = \(.*\)#CFLAGS := \1 $(CFLAGS)#' \
+            -e 's#^CXXFLAGS = \(.*\)#CXXFLAGS := \1 $(CXXFLAGS)#' \
+            -e 's#^LDFLAGS = \(.*\)#LDFLAGS := \1 $(LDFLAGS)#' \
+            -e '/^CFLAGS :=/s# -I${STAGING_INCDIR}/${PYTHON_DIR}##' \
+            -e 's#${RECIPE_SYSROOT}${includedir}#$(includedir)#g' \
+            -e 's#${RECIPE_SYSROOT}${libdir}#$(libdir)#g' \
+            -e 's#${STAGING_BINDIR_NATIVE}/perl-native/perl#perl#g' \
+            -e 's#${STAGING_BINDIR_NATIVE}/python3-native/python3#python3#g' \
+            -e 's#${STAGING_BINDIR_NATIVE}/##g' \
+            -e 's#${TMPDIR}/hosttools/##g' \
+            -e 's#${RECIPE_SYSROOT_NATIVE}##g' \
+            -e 's#${RECIPE_SYSROOT}##g' \
+            -e 's#${WORKDIR}##g' \
+            -e 's#${TMPDIR}##g' \
+            ${D}${libdir}/${BPN}/pgxs/src/Makefile.global
+        # PGXS installs headers and libraries into directories pg_config
+        # reports inside the sysroot, strip the sysroot there
+        sed -i -e '/^pgxsdir = /a pg_sysroot := $(patsubst %$(pgxsdir)/src/makefiles/pgxs.mk,%,$(PGXS))' \
+            ${D}${libdir}/${BPN}/pgxs/src/Makefile.global
+        sed -i -e 's#$(DESTDIR)$(includedir_server)#$(DESTDIR)$(includedir_server:$(pg_sysroot)%=%)#g' \
+            ${D}${libdir}/${BPN}/pgxs/src/makefiles/pgxs.mk
+        sed -i -e 's#$(DESTDIR)$(libdir)#$(DESTDIR)$(libdir:$(pg_sysroot)%=%)#g' \
+            ${D}${libdir}/${BPN}/pgxs/src/Makefile.shlib
     fi
 }
 
+# pg_config is a target binary, stage a script for extension builds
+SYSROOT_PREPROCESS_FUNCS += "postgresql_sysroot_preprocess"
+postgresql_sysroot_preprocess() {
+    install -d ${SYSROOT_DESTDIR}${bindir_crossscripts}
+    sed -e 's,@CROSSSCRIPTS@,${bindir_crossscripts},g' \
+        -e 's,@BINDIR@,${bindir},g' \
+        -e 's,@DOCDIR@,${docdir}/${BPN},g' \
+        -e 's,@INCLUDEDIR@,${includedir},g' \
+        -e 's,@PKGINCLUDEDIR@,${includedir}/${BPN},g' \
+        -e 's,@LIBDIR@,${libdir},g' \
+        -e 's,@PKGLIBDIR@,${libdir}/${BPN},g' \
+        -e 's,@LOCALEDIR@,${datadir}/locale,g' \
+        -e 's,@MANDIR@,${mandir},g' \
+        -e 's,@SHAREDIR@,${datadir}/${BPN},g' \
+        -e 's,@SYSCONFDIR@,${sysconfdir}/${BPN},g' \
+        -e 's,@VERSION@,${PV},g' \
+        ${UNPACKDIR}/pg_config \
+        > ${SYSROOT_DESTDIR}${bindir_crossscripts}/pg_config
+    chmod 0755 ${SYSROOT_DESTDIR}${bindir_crossscripts}/pg_config
+}
+
 SSTATE_SCAN_FILES += "Makefile.global"
 SSTATE_SCAN_FILES:remove = "*_config"
 
-- 
2.43.0

[meta-oe][PATCH 2/3] pgxs.bbclass: add class for PostgreSQL extensions

[Babanpreet Singh]

Extensions that build with PGXS need the same dependency, make
arguments and FILES. Put them in a class so a recipe only has to
inherit pgxs.

Signed-off-by: Babanpreet Singh <bbnpreetsingh@gmail.com>
---
 meta-oe/classes/pgxs.bbclass | 22 ++++++++++++++++++++++
 1 file changed, 22 insertions(+)
 create mode 100644 meta-oe/classes/pgxs.bbclass

diff --git a/meta-oe/classes/pgxs.bbclass b/meta-oe/classes/pgxs.bbclass
new file mode 100644
index 0000000000..b1c7aca264
--- /dev/null
+++ b/meta-oe/classes/pgxs.bbclass
@@ -0,0 +1,22 @@
+#
+# Copyright OpenEmbedded Contributors
+#
+# SPDX-License-Identifier: MIT
+#
+
+# Class for PostgreSQL extensions that are built with PGXS
+
+DEPENDS += "postgresql"
+
+# USE_PGXS is for makefiles that can also build inside the PostgreSQL tree
+EXTRA_OEMAKE:append = " USE_PGXS=1 PG_CONFIG=pg_config"
+
+pgxs_do_install() {
+    oe_runmake install DESTDIR=${D}
+}
+
+EXPORT_FUNCTIONS do_install
+
+FILES:${PN} += "${libdir}/postgresql ${datadir}/postgresql"
+
+RDEPENDS:${PN} += "postgresql"
-- 
2.43.0

[meta-oe][PATCH 3/3] pg-cron: add recipe

[Babanpreet Singh]

pg_cron is a cron-based job scheduler that runs inside PostgreSQL.

Signed-off-by: Babanpreet Singh <bbnpreetsingh@gmail.com>
---
 .../packagegroups/packagegroup-meta-oe.bb           |  1 +
 meta-oe/recipes-dbs/pg-cron/pg-cron_1.6.8.bb        | 13 +++++++++++++
 2 files changed, 14 insertions(+)
 create mode 100644 meta-oe/recipes-dbs/pg-cron/pg-cron_1.6.8.bb

diff --git a/meta-oe/recipes-core/packagegroups/packagegroup-meta-oe.bb b/meta-oe/recipes-core/packagegroups/packagegroup-meta-oe.bb
index ebd66425de..7fba0722b9 100644
--- a/meta-oe/recipes-core/packagegroups/packagegroup-meta-oe.bb
+++ b/meta-oe/recipes-core/packagegroups/packagegroup-meta-oe.bb
@@ -214,6 +214,7 @@ RDEPENDS:packagegroup-meta-oe-dbs = "\
     libdbi \
     lmdb \
     mariadb \
+    pg-cron \
     postgresql \
     psqlodbc \
     rocksdb \
diff --git a/meta-oe/recipes-dbs/pg-cron/pg-cron_1.6.8.bb b/meta-oe/recipes-dbs/pg-cron/pg-cron_1.6.8.bb
new file mode 100644
index 0000000000..9f9a59b398
--- /dev/null
+++ b/meta-oe/recipes-dbs/pg-cron/pg-cron_1.6.8.bb
@@ -0,0 +1,13 @@
+SUMMARY = "Run periodic jobs in PostgreSQL"
+DESCRIPTION = "pg_cron is a cron-based job scheduler that runs inside \
+PostgreSQL as an extension. Jobs are scheduled from SQL with the usual \
+cron syntax and run by a background worker in the server."
+HOMEPAGE = "https://github.com/citusdata/pg_cron"
+BUGTRACKER = "https://github.com/citusdata/pg_cron/issues"
+LICENSE = "PostgreSQL"
+LIC_FILES_CHKSUM = "file://LICENSE;md5=b70f68d78dbcc36c5654d795cee59b0d"
+
+SRC_URI = "git://github.com/citusdata/pg_cron.git;protocol=https;branch=main;tag=v${PV}"
+SRCREV = "2323864075a50f234235f98e567eb958f923b2b3"
+
+inherit pgxs
-- 
2.43.0