Need Assistance: Command to display procedures does not work

On 2/28/24 07:53, Sasmit Utkarsh wrote:

Hi Postgresql Team,

Getting error while executing the below \df command to list the
procedures/functions. whereas query gives the appropriate results Please
assist on how to troubleshoot this.

[sutkarsh-a@dxctravel.svcs.entsvcs.com@zuccmshcd2 data-exports]$ psql
"host=10.166.18.116 port=5432 dbname=shc_data user=shc_admin
password=abc123 sslmode=require"
psql (10.23, server 14.9)
WARNING: psql major version 10, server major version 14.
         Some psql features might not work.

The above is not enough of a hint?

Use psql version 14 as:

https://www.postgresql.org/docs/11/release-11.html

Replace system catalog pg_proc's proisagg and proiswindow columns with
prokind (Peter Eisentraut)

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

*shc_data=> \df*
*ERROR:  column p.proisagg does not exist
LINE 6:   WHEN p.proisagg THEN 'agg'
               ^
HINT:  Perhaps you meant to reference the column "p.prolang".*
*
*
shc_data=> S*ELECT proname AS function_name,proacl AS privileges FROM
pg_proc WHERE proname in
('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');*
       function_name       | privileges
---------------------------+------------
 sql_select_size_procedure |
 sql_select_data_procedure |
 sql_insert_data_procedure |
 sql_update_data_procedure |
(4 rows)

Regards,
Sasmit Utkarsh
+91-7674022625

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

Thanks for the info, But I have another question: I could see the below
functions list with the help on query execution on psql command line

shc_data=> S*ELECT proname AS function_name,proacl AS privileges FROM
pg_proc WHERE proname in
('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');*
function_name | privileges
---------------------------+------------
sql_select_size_procedure |
sql_select_data_procedure |
sql_insert_data_procedure |
sql_update_data_procedure |
(4 rows)

but when I try to connect to the same db on PgAdmin4, I don't see the list
even after refresh. Why is that different?

[image: image.png]

Regards,
Sasmit Utkarsh
+91-7674022625

On Wed, Feb 28, 2024 at 9:33 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On Feb 28, 2024, at 8:53 AM, Sasmit Utkarsh <utkarshsasmit@gmail.com> wrote:

Hi Postgresql Team,

Getting error while executing the below \df command to list the procedures/functions. whereas query gives the appropriate results Please assist on how to troubleshoot this.

[sutkarsh-a@dxctravel.svcs.entsvcs.com@zuccmshcd2 data-exports]$ psql "host=10.166.18.116 port=5432 dbname=shc_data user=shc_admin password=abc123 sslmode=require"
psql (10.23, server 14.9)
WARNING: psql major version 10, server major version 14.
Some psql features might not work.

The version difference between your psql client installation and the server installation is the problem.

On 2/28/24 08:52, Sasmit Utkarsh wrote:

Hi Adrian,

Thanks for the info, But I have another question: I could see the below
functions list with the help on query execution on psql command line

  shc_data=> S*ELECT proname AS function_name,proacl AS privileges FROM
pg_proc WHERE proname in
('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');*
       function_name       | privileges
---------------------------+------------
 sql_select_size_procedure |
 sql_select_data_procedure |
 sql_insert_data_procedure |
 sql_update_data_procedure |
(4 rows)

but when I try to connect to the same db on PgAdmin4, I don't see the
list even after refresh. Why is that different?

1) Are you sure you are connecting to correct database.

2) If I followed your previous posts correctly those functions where not
in the public schema you are looking at in pgAdmin4. They where in the
shc(?) schema which I am pretty sure is the second schema in the
Schemas(2) in the object tree.

Thet
image.png

Regards,
Sasmit Utkarsh
+91-7674022625

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks Rob

So which one could I rely on for better information? probably, results
shown by PgAdmin4 right? As I get the below when I try to drop procedure
through psql cmd and i see the procedure getting fetched from the query

shc_data=> SELECT proname AS function_name,proacl AS privileges FROM
pg_proc WHERE proname in
('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');
function_name | privileges
---------------------------+------------
sql_select_size_procedure |
sql_select_data_procedure |
*sql_insert_data_procedure* |
sql_update_data_procedure |
(4 rows)

shc_data=> DROP PROCEDURE *sql_insert_data_procedure*;
ERROR: could not find a procedure named "sql_insert_data_procedure"
shc_data=>

Regards,
Sasmit Utkarsh
+91-7674022625

On Wed, Feb 28, 2024 at 10:25 PM Rob Sargent <robjsargent@gmail.com> wrote:

On 2/28/24 09:08, Sasmit Utkarsh wrote:

Thanks Rob

So which one could I rely on for better information? probably, results
shown by PgAdmin4 right? As  I get the below when I try to drop
procedure through psql cmd and i see the procedure getting fetched from
the query

shc_data=> SELECT proname AS function_name,proacl AS privileges FROM
pg_proc WHERE proname in
('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');
       function_name       | privileges
---------------------------+------------
 sql_select_size_procedure |
 sql_select_data_procedure |
*sql_insert_data_procedure* |
 sql_update_data_procedure |
(4 rows)

shc_data=> DROP PROCEDURE *sql_insert_data_procedure*;
ERROR:  could not find a procedure named "sql_insert_data_procedure"
shc_data=>

We've been down this road before, it is a search_path issue.

In psql do:

SHOW search_path;

then do:

\df *.sql_insert_data_procedure

Then see if the schema shown in the second command is in the search_path
returned by the first command.

Regards,
Sasmit Utkarsh
+91-7674022625

--
Adrian Klaver
adrian.klaver@aklaver.com

Hello Adria/Rob,

Please note, 'shc' schema was part of a different machine/host. For now, I
see issue on another host machine which i connect using psql and PgAdmin4
and get different result on both and we have only public schema here on
this machine

shc_data=> SELECT proname AS function_name,proacl AS privileges FROM
pg_proc WHERE proname in
('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');
function_name | privileges
---------------------------+------------
sql_select_size_procedure |
sql_select_data_procedure |
sql_insert_data_procedure |
sql_update_data_procedure |
(4 rows)

shc_data=> DROP PROCEDURE sql_insert_data_procedure;
ERROR: could not find a procedure named "sql_insert_data_procedure"
shc_data=> SHOW search_path
shc_data->
shc_data-> ^C
shc_data=> SHOW search_path;
search_path
-----------------
"$user", public
(1 row)

shc_data=>

Regards,
Sasmit Utkarsh
+91-7674022625

On Wed, Feb 28, 2024 at 10:43 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/28/24 09:13, Sasmit Utkarsh wrote:

Reply to list also
Ccing list

HI Adrian,

Related to shc shema, they were part of different host/machine.
Currently, I'm seeing the result for this discrepancy on another server
machine where we have only a public schema.

1) I doubt that if this is the database you are showing in your pgAdmin4
screenshot.

2) The information I asked for in my previous post is still relevant and
needs answering.

3) Add pronamespace::regnamespace to your SELECT query to get the schema
names for the the functions.

Regards,
Sasmit Utkarsh
+91-7674022625

On Wed, Feb 28, 2024 at 10:35 PM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 2/28/24 08:52, Sasmit Utkarsh wrote:

Hi Adrian,

Thanks for the info, But I have another question: I could see the

below

functions list with the help on query execution on psql command line

    shc_data=> S*ELECT proname AS function_name,proacl AS

privileges FROM

pg_proc WHERE proname in

('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');*

         function_name       | privileges
---------------------------+------------
   sql_select_size_procedure |
   sql_select_data_procedure |
   sql_insert_data_procedure |
   sql_update_data_procedure |
(4 rows)

but when I try to connect to the same db on PgAdmin4, I don't see

the

list even after refresh. Why is that different?

1) Are you sure you are connecting to correct database.

2) If I followed your previous posts correctly those functions where
not
in the public schema you are looking at in pgAdmin4. They where in the
shc(?) schema which I am pretty sure is the second schema in the
Schemas(2) in the object tree.

  Thet
image.png

Regards,
Sasmit Utkarsh
+91-7674022625

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

Sorry, Yes I did connect to the correct DB and it's just a test database
and there's no shc schema. After giving the pronamespace::regnamespace. I
do see it has another schema as test. Thanks a lot for your guidance, will
make next time i verify with this details first, switching between
different DBs and machines got my mind diverted

[image: image.png]

Regards,
Sasmit Utkarsh
+91-7674022625

On Wed, Feb 28, 2024 at 10:50 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/28/24 10:15, Sasmit Utkarsh wrote:

Hi Adrian,

Sorry, Yes I did connect to the correct DB and it's just a test database
and there's no shc schema. After giving the pronamespace::regnamespace.
I do see it has another schema as test. Thanks a lot for your guidance,

pgAdmin4 was telling you that with the Schemas(2) in the Object Explorer.

will make next time i verify with this details first, switching between
different DBs and machines got my mind diverted

image.png

Regards,
Sasmit Utkarsh
+91-7674022625

--
Adrian Klaver
adrian.klaver@aklaver.com