fix: support PostgreSQL 17

[pdecat]

This PR adds support for compiling FDW standalone plugins against PosgreSQL 17.

ARG GO_VERSION="1.24.4"
ARG POSTGRES_VERSION="17.5"
ARG POSTGRES_SHORT_VERSION="17"
ARG STEAMPIPE_PLUGIN_AWS_VERSION="1.14.1"

# FDW Builder Base Stage - Common base for building all FDW plugins
FROM postgres:${POSTGRES_VERSION} AS fdw-builder-base
	ARG GO_VERSION
	ARG POSTGRES_SHORT_VERSION

	# Install PostgreSQL packages and build tools
	RUN apt-get update && apt-get install -y \
		build-essential \
		curl \
		git \
		make \
		postgresql-server-dev-${POSTGRES_SHORT_VERSION} \
		rsync \
		tar \
		wget \
		&& apt-get clean

	# Install Go
	RUN wget -q https://go.dev/dl/go${GO_VERSION}.linux-amd64.tar.gz -O go.tar.gz && \
		tar -C /usr/local -xzf go.tar.gz && \
		rm go.tar.gz

	# Set PostgreSQL environment variables for compilation
	ENV PATH="/usr/local/go/bin:$PATH"
	ENV PG_CONFIG="/usr/bin/pg_config"
	ENV PGXS="/usr/lib/postgresql/${POSTGRES_SHORT_VERSION}/lib/pgxs/src/makefiles/pgxs.mk"

	WORKDIR /build

	# Clone the latest steampipe-postgres-fdw repository
	RUN git clone https://github.com/pdecat/steampipe-postgres-fdw.git --branch pg17 .

	# Build GCP FDW extension
	RUN go mod download

# AWS FDW Builder Stage
FROM fdw-builder-base AS aws-fdw-builder
	ARG STEAMPIPE_PLUGIN_AWS_VERSION

	# Build AWS FDW extension
	RUN make standalone plugin=aws plugin_version=v${STEAMPIPE_PLUGIN_AWS_VERSION} plugin_github_url=github.com/turbot/steampipe-plugin-aws

	# Verify build artifacts
	RUN ls -la build-Linux/ && \
		test -f build-Linux/steampipe_postgres_aws.so && \
		test -f build-Linux/steampipe_postgres_aws.control && \
		test -f build-Linux/steampipe_postgres_aws--1.0.sql

# Runtime Stage - PostgreSQL with FDW Extensions
FROM postgres:${POSTGRES_VERSION}
	ARG POSTGRES_SHORT_VERSION
	ARG STEAMPIPE_PLUGIN_AWS_VERSION

	ENV POSTGRES_LIBDIR="/usr/lib/postgresql/${POSTGRES_SHORT_VERSION}/lib/"
	ENV POSTGRES_EXTDIR="/usr/share/postgresql/${POSTGRES_SHORT_VERSION}/extension/"

	# Install common certificate authorities
	RUN apt-get update && apt-get install -y \
		ca-certificates \
		&& apt-get clean

	# Install AWS FDW extension for PostgreSQL
	COPY --from=aws-fdw-builder /build/build-Linux/steampipe_postgres_aws.so ${POSTGRES_LIBDIR}
	COPY --from=aws-fdw-builder /build/build-Linux/steampipe_postgres_aws.control ${POSTGRES_EXTDIR}
	COPY --from=aws-fdw-builder /build/build-Linux/steampipe_postgres_aws--1.0.sql ${POSTGRES_EXTDIR}

[pskrbasu]

Hey @pdecat, Thank you for taking the time and effort to get this done. I will get it reviewed by this week.

[pdecat]

Hi @pskrbasu,

I may have uncovered some further things to adjust with PostgreSQL 17+, as some queries which were working with PostgreSQL 14.17 (Steampipe's embedded) and PostgreSQL 15.13 (FDW standalone) are failing with an unrecognized node type: 315 error:

ERROR:  XX000: unrecognized node type: 315
LOCATION:  expression_tree_walker_impl, nodeFuncs.c:2660
STATEMENT:  SELECT

With 315 being T_RestrictInfo in PG17.x:

#  grep 315 /usr/include/postgresql/17/server/nodes/nodetags.h
        T_RestrictInfo = 315,

I've managed to work-around this issue with this sort of change:

 WITH ka AS (
   SELECT
-    substring(aliases[0] ->> 'AliasName' FROM 7) AS "alias", -- aliases start with 'alias/'
+    substring(coalesce(aliases[0], '{}'::jsonb) ->> 'AliasName' FROM 7) AS "alias",  -- aliases start with 'alias/'
+
     id,
     region
   FROM aws_kms_key

I'm now trying to figure out if this is a regression in PG17.x or something that should be done in Steampipe FDW and/or plugin code. It seems to happen when hydrate functions return nil values for JSON type. Edit: it also happens without JSON columns involved.

Note: I've also tested 18beta1 where the issue is still present but with 315 instead of 318.

[pdecat]

I've reproduced the issue with PostgreSQL 16.9 with 298 instead of 315 (still T_RestrictInfo):

ERROR:  XX000: unrecognized node type: 298
LOCATION:  expression_tree_walker_impl, nodeFuncs.c:2539
STATEMENT:  SELECT

grep 298 /usr/include/postgresql/16/server/nodes/nodetags.h
        T_RestrictInfo = 298,

[pdecat]

Searching for "unrecognized node type" "RestrictInfo", I found this:

RestrictInfo is not a general expression node and support for it has been deliberately omitted from expression_tree_walker(). So I think what you are proposing is a bad idea and probably a band-aid for some other bad idea.

https://www.postgresql.org/message-id/34353.1430250604%40sss.pgh.pa.us

It seems the FDW code adding those RestrictInfo nodes needs to be changed to support PostgreSQL 16+ as the PostgreSQL expression_tree_walker_impl code won't be updated to handle them.

[pdecat]

It also happens without JSON columns involved, e.g. here's the smallest reproduction case I could come with so far:

ERROR:  XX000: unrecognized node type: 315
LOCATION:  expression_tree_walker_impl, nodeFuncs.c:2660
STATEMENT:  SELECT p.project_id
        FROM
          gcp_organization_project AS p
        LEFT OUTER JOIN
          gcp_organization AS o
          ON
            p.project_id
            = o.name 

PS: yes, that query doesn't make any sense.

[pdecat]

So, I've finally managed to eliminate the unrecognized node type: 315 errors using the extract_actual_clauses which strips those RestrictInfo nodes:

/*
 * extract_actual_clauses
 *
 * Extract bare clauses from 'restrictinfo_list', returning either the
 * regular ones or the pseudoconstant ones per 'pseudoconstant'.
 * Constant-TRUE clauses are dropped in any case.
 */

It's mentioned here too:

In simple cases the FDW can just strip RestrictInfo nodes from the scan_clauses list (using extract_actual_clauses)

https://www.postgresql.org/docs/current/fdw-planning.html

[pdecat]

Hmm, also getting the following error with some other queries:

2025-07-21 21:25:29.333 UTC [221671] ERROR:  XX000: unrecognized node type: 318
2025-07-21 21:25:29.333 UTC [221671] CONTEXT:  SQL statement "SELECT 'APPLICATION_GATEWAY' AS subtype, row_to_json(r) AS data FROM (WITH ip_configs AS (
          SELECT
            json_build_object(
              'publicIpAddressId', pip.id,
              'publicIpAddress', coalesce(pip.ip_address::text, '')
            ) AS public_ip,
            json_build_object(
              'privateIpAllocationMethod', coalesce(config -> 'properties' ->> 'privateIPAllocationMethod', ''),
              'privateIpAddress', coalesce(config -> 'properties' ->> 'privateIPAddress'::text, '')
            ) AS private_ip,
            appgw.id,
            pip.id AS "pip_id"
          FROM azure_application_gateway AS appgw,
            jsonb_array_elements(appgw.frontend_ip_configurations) AS config
          LEFT OUTER JOIN azure_public_ip AS pip ON lower(pip.id) = lower(config -> 'properties' -> 'publicIPAddress' ->> 'id')
        )

        SELECT
          -- common
          appgw.name,
          appgw.id AS "resourceId",
          appgw.type AS "resourceType",
          appgw.resource_group AS "resourceGroup",
          appgw.region AS "location",
          appgw.tags AS "cloudTags",
          -- specific
          appgw.sku,
          json_build_array(json_build_object(
            'publicIpAddress', coalesce(pub_conf.public_ip, json_build_object()),
            'privateIpAddress', coalesce(prv_conf.private_ip, json_build_object())
          )) AS "frontendIpConfigurations",
          json_build_object(
            'policyName', coalesce(appgw.ssl_policy ->> 'policyName', ''),
            'policyType', coalesce(appgw.ssl_policy ->> 'policyType', '')
          ) AS "sslPolicy",
          appgw.enable_http2 AS "enableHttp2"
        FROM azure_application_gateway AS appgw
        LEFT OUTER JOIN ip_configs AS pub_conf ON lower(pub_conf.id) = lower(appgw.id) AND pub_conf.pip_id IS NOT NULL
        LEFT OUTER JOIN ip_configs AS prv_conf ON lower(prv_conf.id) = lower(appgw.id) AND prv_conf.pip_id IS NULL
        ) r"

# grep 318 /usr/include/postgresql/17/server/nodes/nodetags.h
        T_OuterJoinClauseInfo = 318,

[github-actions[bot]]

This PR is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[pdecat]

Still working on this...

[github-actions[bot]]

This PR is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[pdecat]

Still working on this...