Add pl/pgsql debugging support #11143
Replies: 8 comments 9 replies
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
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? |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
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;"
EOFhttps://gist.github.com/segevfiner/faf1bbacbf8a942635c0115669d6a718 |
Beta Was this translation helpful? Give feedback.
-
|
I definitely agree. Debugging existing functions is downright painful. I'm sure debugging triggers would be even more difficult. |
Beta Was this translation helpful? Give feedback.
-
|
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? |
Beta Was this translation helpful? Give feedback.
-
|
Note that it needs to run in your project root to read the supabase config file |
Beta Was this translation helpful? Give feedback.
-
|
Oh, yes it couldn't find the package: |
Beta Was this translation helpful? Give feedback.
-
|
Guessing because the docker image is not longer |
Beta Was this translation helpful? Give feedback.
-
|
Hmm I think I tried it with Postgresql 17. Maybe it's named differently in that version's repository or not available in it. |
Beta Was this translation helpful? Give feedback.
-
|
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 |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
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.
Beta Was this translation helpful? Give feedback.
All reactions