Add pl/pgsql debugging support

[wiverson]

Feature request

https://github.com/EnterpriseDB/pldebugger provides for an API & client for debuging pl/pgsql.

Would be great to have this as an out-of-the-box option for the local dev environment in particular. Live projects would be great as well, of course.

Is your feature request related to a problem? Please describe.

As PostgREST allows pl/pgsql many interesting options, those become increasingly more challenging to support/debug. At best it's a lot of guess-work and returning text intermediate steps to try to figure out what's going on.

Describe the solution you'd like

I think (based on the description of pldebugger) that this would involve both build option tweaks as well as a major improvements to the Supabase dev studio UI.

Describe alternatives you've considered

Right now it's basically console logging at best.

[schox]

In the meantime, is it possible/advisable to try and install it manually somehow? It can be painful debugging pl/pgsql after the fact with logging/notices etc.

[AlfredPianist]

I was wondering exactly the same thing as @schox and @wiverson. Have any of you figured something out related to pldebugger all these months or no idea how to do that?

I tried installing it directly in the running container following the README and I encountered a lot of errors, especially regarding the non-existent C headers of PostgreSQL which I find strange and I don't know how to go from there. Any ideas?

[instantgis]

I am also looking forward to having debug capability for functions. Currently my workflow is to develop the SQL code using DO blocks in pgAdmin and use raise notice to debug. Then I turn the DO block into a function. But its not so good when you discover an issue after you created a function for now you have to go back to the DO block which you hopefully saved. Having said that I find that the debugger input dialog in pgAmin 4 will not accept array inputs that are perfectly legit for my function inputs. All an all pldebugger would greatly improve developer experience. I see Supabase local is based on a docker image for Postgres. Is it not simply a question of including the debug plugin in there? EnterpriseDB does it out of the box.

[segevfiner]

This should basically involve adding the pldebugger extension to the available builtin extensions and the option to edit the config file to load it as needed. I guess it should be possible to script the process to install this into a running postgres container from the supabase CLI.

[segevfiner]

You can try this shell script on the local development instance:

#!/bin/bash
set -ex

project_id=$(sed -nr 's/project_id[[:space:]]*=[[:space:]]*"([^"]+)"/\1/p' supabase/config.toml)
major_version=$(sed -nr 's/major_version[[:space:]]*=[[:space:]]*([[:digit:]]+)/\1/p' supabase/config.toml)

docker exec -i supabase_db_$project_id bash <<EOF
set -x
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

apt-get install -y postgresql-$major_version-pldebugger
psql -h localhost postgres supabase_admin -c "create extension pldbgapi with schema extensions;"
EOF

https://gist.github.com/segevfiner/faf1bbacbf8a942635c0115669d6a718

[rodpatulski]

I definitely agree. Debugging existing functions is downright painful. I'm sure debugging triggers would be even more difficult.
The only workaround i have now is to use a bunch of temp tables in functions and not drop them at the end of the function definition. Then I can at least select from them to see what the data looked like as the function ran.

[amerryma]

I've been trying to do this. I already know how to make supabase cli look at my custom supabase/postgres image, but I'm struggling to add the actual extension to the existing Dockerfile and building the image. Can someone give this a shot or point me in the right direction on how to do it?

[segevfiner]

Note that it needs to run in your project root to read the supabase config file

[amerryma]

Oh, yes it couldn't find the package: Unable to locate package postgresql-15-pldebugger

[amerryma]

Guessing because the docker image is not longer bookworm.

[segevfiner]

Hmm I think I tried it with Postgresql 17. Maybe it's named differently in that version's repository or not available in it.

[segevfiner]

It's using pgdg, so should have a builsd for that distro as well. Maybe need to stick an apt-get update there because maybe the older pgdg shell script didn't run it for you

[jdoklovic]

Unfortunately none of the above suggestions will work because it seems supabase uses nix to install postgres and it's extensions.

This means when you try to manually install, it goes into the wrong place. i.e. nix has it's own directories where it expects to find extensions and libs.

In theory it should be easy to add the pldebugger extension to the nix files and get it installed, but I don't know enough about it to actually do it :(

I can't believe we still can't debug postgres functions locally with supabase.

[amerryma]

Yes, I believe this was my issue, I never got it to work. I'm in the same boat as you. I don't know enough about nix to get it to work properly.

[amerryma]

Hi all, I started working on this finally. If anyone can let me know if this is the right direction, that would be greatly appreciated!

supabase/postgres@develop...amerryma:postgres:develop

[Jeidoban]

While @amerryma's PR would be the ideal solution here when this repo isn't frozen for new extensions, I hacked together a script with Claude code that replaces your local postgres image with one that installs the debugger and is compatible with the new nix build system. This essentially uses your supabase postgers image as a base, and creates a new Docker image that pulls the debugger from github, builds it and puts the files where nix would were it properly added to the supabase postgres image. Run this with Supabase running so it can detect your current Postgres container, then add the extension (CREATE EXTENSION IF NOT EXISTS pldbgapi;). Again this was mostly done with AI so use at your own risk.

#!/usr/bin/env bash
# enable-pg-debugger.sh
#
# Adds pldebugger (pgAdmin PL/pgSQL debugger support) to your local Supabase
# postgres image without forking any repos.
#
# How it works:
#   1. Finds the postgres image your Supabase CLI is already using
#   2. Builds a thin wrapper image (same tag) that compiles + installs pldebugger
#   3. Adds plugin_debugger to shared_preload_libraries in the baked-in conf
#   4. Restarts Supabase so postgres picks it up
#
# Usage: copy this script to your project root (the folder that CONTAINS the
#   supabase/ directory), then run:
#   ./enable-pg-debugger.sh
#
# To revert: docker pull <original-image-tag>
# Then: supabase stop && supabase start

set -euo pipefail

# ── helpers ───────────────────────────────────────────────────────────────────

log()  { printf '  ▸ %s\n' "$*"; }
die()  { printf '\n  ✗ %s\n\n' "$*" >&2; exit 1; }

echo ""
echo "  Supabase postgres debugger setup"
echo "  ─────────────────────────────────"
echo ""

# ── sanity checks ─────────────────────────────────────────────────────────────

command -v docker   &>/dev/null || die "docker not found in PATH"
command -v supabase &>/dev/null || die "supabase CLI not found in PATH"
[[ -f supabase/config.toml ]]  || die "No supabase/config.toml found — run from your project root"

# ── find the postgres image the CLI is using ──────────────────────────────────

PROJECT_NAME=$(basename "$PWD")
DB_CONTAINER="supabase_db_${PROJECT_NAME}"

if ! docker inspect "$DB_CONTAINER" &>/dev/null; then
  log "Supabase not running — starting it to detect the postgres image..."
  supabase start
fi

POSTGRES_IMAGE=$(docker inspect "$DB_CONTAINER" --format '{{.Config.Image}}')
log "Postgres image: ${POSTGRES_IMAGE}"

# ── idempotency: skip if already patched ──────────────────────────────────────

if docker inspect "$POSTGRES_IMAGE" \
     --format '{{index .Config.Labels "pldebugger"}}' 2>/dev/null \
   | grep -q "true"; then
  log "Image already has pldebugger — nothing to do."
  echo ""
  exit 0
fi

# ── detect OS ─────────────────────────────────────────────────────────────────

if docker run --rm --entrypoint="" "$POSTGRES_IMAGE" \
     sh -c 'command -v apt-get' &>/dev/null; then
  OS=debian
elif docker run --rm --entrypoint="" "$POSTGRES_IMAGE" \
       sh -c 'command -v apk' &>/dev/null; then
  OS=alpine
else
  die "Cannot detect package manager in ${POSTGRES_IMAGE}"
fi
log "Image OS: ${OS}"

# ── generate Dockerfile ───────────────────────────────────────────────────────

WORKDIR=$(mktemp -d)
trap 'rm -rf "$WORKDIR"' EXIT

# FROM line needs variable expansion; everything else uses single-quoted
# heredocs so $CONF and other in-container shell vars are written literally.
printf 'FROM %s\n' "$POSTGRES_IMAGE" > "$WORKDIR/Dockerfile"

if [[ "$OS" == "debian" ]]; then
  cat >> "$WORKDIR/Dockerfile" << 'DOCKERFILE'

# pg_config --includedir-server returns /usr/include/server, which doesn't
# exist in this Nix-based image. --pgxs does point into the Nix store
# correctly, so we derive the real path from it and symlink /usr/include/server
# there. That way plain `make USE_PGXS=1` finds postgres.h without any CFLAGS
# override. libssl-dev provides the OpenSSL headers pulled in by libpq-be.h.
RUN apt-get update -qq && \
    apt-get install -y --no-install-recommends \
      build-essential libssl-dev libkrb5-dev curl && \
    PG_NIX_DIR=$(pg_config --pgxs | sed 's|/lib/pgxs.*||') && \
    ln -sf "${PG_NIX_DIR}/include/server"   /usr/include/server && \
    ln -sf "${PG_NIX_DIR}/include/internal" /usr/include/internal && \
    curl -fsSL \
      https://github.com/EnterpriseDB/pldebugger/archive/refs/heads/master.tar.gz \
      | tar -xz && \
    cd pldebugger-master && \
    make USE_PGXS=1 && \
    for dir in $(find /nix/store -name "pgaudit.so" -path "*postgresql-and-plugins*" 2>/dev/null | xargs -I{} dirname {} | sort -u); do \
      cp plugin_debugger.so "$dir/plugin_debugger.so"; \
    done && \
    for dir in $(find /nix/store -name "pgaudit.control" -path "*postgresql-and-plugins*" 2>/dev/null | xargs -I{} dirname {} | sort -u); do \
      cp pldbgapi*.control pldbgapi*.sql "$dir/"; \
    done && \
    cd .. && rm -rf pldebugger-master && \
    apt-get purge -y --auto-remove build-essential libssl-dev libkrb5-dev curl && \
    rm -rf /var/lib/apt/lists/*
DOCKERFILE

else
  # Alpine: Nix store already has the OpenSSL headers postgres was compiled
  # against; use them directly to avoid a musl/glibc OpenSSL mismatch.
  cat >> "$WORKDIR/Dockerfile" << 'DOCKERFILE'

RUN apk add --no-cache --virtual .build-deps build-base curl && \
    OPENSSL_INC=$(find /nix/store -name ssl.h -path '*/openssl/ssl.h' 2>/dev/null \
      | head -1 | xargs dirname | xargs dirname) && \
    curl -fsSL \
      https://github.com/EnterpriseDB/pldebugger/archive/refs/heads/master.tar.gz \
      | tar -xz && \
    cd pldebugger-master && \
    make USE_PGXS=1 CFLAGS="-I${OPENSSL_INC}" && \
    make USE_PGXS=1 install && \
    cd .. && rm -rf pldebugger-master && \
    apk del .build-deps
DOCKERFILE
fi

# Shared: patch postgresql.conf + label the image so we can detect it later
cat >> "$WORKDIR/Dockerfile" << 'DOCKERFILE'

# Add plugin_debugger to shared_preload_libraries.
# Uses [^']* to stop at the closing quote, preserving any trailing comment.
# Idempotent: no-ops if plugin_debugger is already present.
RUN CONF=/etc/postgresql/postgresql.conf && \
    if ! grep -q 'plugin_debugger' "$CONF" 2>/dev/null; then \
      if grep -q "^shared_preload_libraries" "$CONF" 2>/dev/null; then \
        sed -i \
          "s|^shared_preload_libraries = '\([^']*\)'|shared_preload_libraries = '\1, plugin_debugger'|" \
          "$CONF"; \
      else \
        echo "shared_preload_libraries = 'plugin_debugger'" >> "$CONF"; \
      fi; \
    fi

LABEL pldebugger="true"
DOCKERFILE

# ── build ─────────────────────────────────────────────────────────────────────

log "Building image with pldebugger (takes a couple of minutes)..."
docker build --tag "$POSTGRES_IMAGE" "$WORKDIR"

# ── restart ───────────────────────────────────────────────────────────────────

log "Restarting Supabase..."
supabase stop
supabase start

# ── done ──────────────────────────────────────────────────────────────────────

echo ""
echo "  Done! Enable the debugger extension in your database:"
echo ""
echo "    psql -h localhost -U postgres -c 'CREATE EXTENSION IF NOT EXISTS pldbgapi;'"
echo ""
echo "  Then in pgAdmin: right-click a function → Debugging."
echo ""
echo "  To revert to the original image:"
echo "    docker pull ${POSTGRES_IMAGE} && supabase stop && supabase start"
echo ""