Cannot debug function from database explorer due to missing extension plpgsql

[stevenmccormack]

Describe the bug
When I try to debug a function by right clicking and selecting "Debug Routine" in the explorer I receive the following error:

To Reproduce
Steps to reproduce the behavior:

1. Create a schema and function to debug:

create schema if not exists test_schema;

create or replace function test_schema.debug_test() returns text as $$
begin
  return 'Test';
end;
$$ language plpgsql;

3. Richt click 'debug_test' in database explorer
4. Select 'Debug Routine'

Expected behavior
Function is debugged

Desktop (please complete the following information):

• OS: Debian Linux Kernel 5.17.0-1-amd64
• DataGrip 2022.1.1
• PostgreSQL Debugger 221.1.2

Additional context
Debugging the following from a console window works as expected:

select test_schema.debug_test();

[ng-galien]

Did you install the debugger extension on your database?

CREATE EXTENSION if not exists pldbgapi;

If it's installed please give me the result of

SELECT
t_namespace.nspname,
t_extension.extname,
t_extension.extversion
FROM pg_extension t_extension
JOIN pg_namespace t_namespace ON t_extension.extnamespace = t_namespace.oid

[stevenmccormack]

Yes, the extension is installed. Debugging works from a console window. The error only occurs when debugging a function from the context menu in database explorer.

Please find below the requested output:

nspname	extname	extversion
pg_catalog	plpgsql	1.0
public	pg_trgm	1.6
public	uuid-ossp	1.1
public	pldbgapi	1.1

[ng-galien]

Hi @stevenmccormack, I can't reproduce the problem yet.

The problem seems to be a search_path issue, in fact the dialog displays all detected extensions for the session. It shows only plpgsql installed on the builtin pg_catalog schema. All extensions installed on public schema are not available with the session created from the database tree, they are not displayed into the dialog. The basic solution is to make public schema available from your session or installing debugger extension to your target schema. I will investigate this issue to find a fix.

• So please give me the pg version you're using.
• I also need your Intellij build version (e.g Build #IU-221.5080.210, built on April 12, 2022)

Then, In the datasource config, check if both public and test_schema is introspected or reset introspection.

[stevenmccormack]

Hi @ng-galien,
thanks for the update. Both schemas have been introspected. The server is running DBMS: PostgreSQL (ver. 14.2 (Debian 14.2-1.pgdg110+1)) Case sensitivity: plain=lower, delimited=exact Driver: PostgreSQL JDBC Driver (ver. 42.3.3, JDBC4.2).
My IDE version is Build #DB-221.5080.224, built on April 13, 2022

[ng-galien]

Hi @stevenmccormack

I've tried to reproduce the problem.

IDEA: Build #IU-221.5080.210, built on April 12, 2022
OS: Darwin MacBook-Pro 20.6.0 Darwin Kernel Version 20.6.0: Tue Feb 22 21:10:41 PST 2022; root:xnu-7195.141.26~1/RELEASE_X86_64 x86_64
PG: PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

PG use my customized image

docker run -p 5514:5432 --name PG14-debug -e POSTGRES_PASSWORD=postgres -d galien0xffffff/postgres-debugger:14

I run the command

CREATE EXTENSION IF NOT EXISTS pldbgapi;
CREATE SCHEMA IF NOT EXISTS test_schema;
CREATE OR REPLACE FUNCTION test_schema.debug_test() RETURNS TEXT AS
$$
BEGIN
RETURN 'Test';
END;
$$ LANGUAGE plpgsql;

And the problem does not appears yet.

[ng-galien]

@stevenmccormack
I think will 221.1.3 fix this issue

[stevenmccormack]

Hi @ng-galien,

I just tried the new version and can confirm 221.1.3 fixed it for me.

Thanks!