Clarity regarding the procedures call in postgresql for public and non-public schema

[Sasmit Utkarsh <utkarshsasmit@xxxxxxxxx>]

Hi Postgresql Team,

***Facing the below issue, while calling the below procedures in public and non-public schema shc for ( shc_uadmin user)

[shc_user@cucmtpccu1 ~]$ export PGHOST=cucmpsgsu0.postgres.database.azure.com
[shc_user@cucmtpccu1 ~]$ export PGDATABASE=mshcd
[shc_user@cucmtpccu1 ~]$ export PGUSER=shc_uadmin
[shc_user@cucmtpccu1 ~]$ export PGPASSWORD=abc123
[shc_user@cucmtpccu1 ~]$ export PGOPTIONS='--search_path=shc'
[shc_user@cucmtpccu1 ~]$ .nix-profile/bin/test 'K-T-*' -q
<6>0164503{00000000-0000-0000-0000-000000000000.noterm}-[INFO] BUILD_TAG = jenkins-Tpfasmbuild1-516
<6>0164503{00000000-0000-0000-0000-000000000000.noterm}-[INFO] sql_tty_connect_db: tty_conninfo:'dbname=shc_tty_data'
<6>0164503{00000000-0000-0000-0000-000000000000.noterm}-[INFO] process_name=10.166.29.36#164503
<7>0164503{00000000-0000-0000-0000-000000000000.noterm}---------------------------------------------------------
<7>0164503{00000000-0000-0000-0000-000000000000.noterm}-            Using PostgreSQL database
<7>0164503{00000000-0000-0000-0000-000000000000.noterm}---------------------------------------------------------

<7>0164503{00000000-0000-0000-0000-000000000000.noterm}-SQL_init_db_connection() conninfo=dbname=shc_data
<7>0164503{00000000-0000-0000-0000-000000000000.noterm}-SQL_init_db_connection() Connection to shc_data database SUCCESSFUL
<6>0164503{00000000-0000-0000-0000-000000000000.noterm}-[INFO] PostgreSQL Server Version = 1500.4 protocol 3
<7>0164503{00000000-0000-0000-0000-000000000000.noterm}-SQL_init_db_connection() PREPARE AdvisoryLockData PQresultStatus = PGRES_COMMAND_OK
<7>0164503{00000000-0000-0000-0000-000000000000.noterm}-SQL_init_db_connection() PREPARE AdvisoryUnLockData PQresultStatus = PGRES_COMMAND_OK
<7>0164503{00000000-0000-0000-0000-000000000000.noterm}-SQL_init_db_connection() PREPARE SelectSize PQresultStatus = PGRES_FATAL_ERROR
<3>0164503{00000000-0000-0000-0000-000000000000.noterm}-[ERROR] PREPARE failed for RIAT! ERROR:  procedure sql_select_size_procedure(text, integer, unknown) does not exist
LINE 1: CALL SQL_select_size_procedure($1, $2, NULL)
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
 [../tpfasm.c:13961:SQL_init_db_connection]
<7>0164503{00000000-0000-0000-0000-000000000000.noterm}-ROLLBACK TRANSACTION

***For (PGUSER=pgddb_admin kind of admin user) , I see an expected message.. Please see below

export PGUSER=pgddb_admin
export PGPASSWORD=xyz123
[shc_user@cucmtpccu1 ~]$ .nix-profile/bin/test 'K-T-*' -q
<6>3429958-[INFO] BUILD_TAG = jenkins-Tpfasmbuild1-515
<6>3429958-[INFO] sql_tty_connect_db: tty_conninfo:'dbname=shc_tty_data'
<6>3429958-[INFO] process_name=10.166.29.36#3429958
<7>3429958---------------------------------------------------------
<7>3429958-            Using PostgreSQL database
<7>3429958---------------------------------------------------------

<7>3429958-SQL_init_db_connection() conninfo=dbname=shc_data
<7>3429958-SQL_init_db_connection() Connection to shc_data database SUCCESSFUL
<6>3429958-[INFO] PostgreSQL Server Version = 1500.4 protocol 3
<7>3429958-SQL_init_db_connection() PREPARE AdvisoryLockData PQresultStatus = PGRES_COMMAND_OK
<7>3429958-SQL_init_db_connection() PREPARE AdvisoryUnLockData PQresultStatus = PGRES_COMMAND_OK
<7>3429958-SQL_init_db_connection() PREPARE SelectSize PQresultStatus = PGRES_COMMAND_OK
<7>3429958-SQL_init_db_connection() PREPARE SelectData PQresultStatus = PGRES_COMMAND_OK
<7>3429958-SQL_init_db_connection() PREPARE InsertData PQresultStatus = PGRES_COMMAND_OK
<7>3429958-SQL_init_db_connection() PREPARE UpdateData PQresultStatus = PGRES_COMMAND_OK

***When i list the procedures, with the help of command line

[shc_user@cucmtpccu1 ~]$ psql "host=cucmpsgsu0.postgres.database.azure.com port=5432 dbname=mshcd user=shc_uadmin password= abc123  sslmode=require options=--search_path=shc"

mshcd=> \df
                                                                                          List of functions
 Schema |           Name            | Result data type |                                                          Argument data types                                                          | Type
--------+---------------------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+------
 shc    | sql_insert_data_procedure |                  | IN fa integer, IN ft integer, IN ord integer, IN xaddr text, IN recid text, IN blk_size integer, IN indata bytea, INOUT outdata bytea | proc
 shc    | sql_select_data_procedure |                  | IN fa integer, IN hold boolean, INOUT blksize integer, INOUT fadata bytea                                                             | proc
 shc    | sql_select_size_procedure |                  | IN hexid text, IN rtp_in integer, INOUT size_data text                                                                                | proc
 shc    | sql_update_data_procedure |                  | IN indata bytea, IN unhold boolean, IN fa integer                                                                                     | proc

mshcd=> 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_insert_data_procedure | {=X/pgddb_admin,pgddb_admin=X/pgddb_admin,shc_uadmin=X/pgddb_admin}
 sql_select_data_procedure | {=X/pgddb_admin,pgddb_admin=X/pgddb_admin,shc_uadmin=X/pgddb_admin}
 sql_select_size_procedure | {=X/pgddb_admin,pgddb_admin=X/pgddb_admin,shc_uadmin=X/pgddb_admin}
 sql_update_data_procedure | {=X/pgddb_admin,pgddb_admin=X/pgddb_admin,shc_uadmin=X/pgddb_admin}
(4 rows)

**Question: Why is the sql_select_size_procedure not getting called/executed when user is "shc_uadmin" but getting executed or called when the user is "pgddb_admin"(admin user), even though there are no changes in the inputs for the procedure call, just changing the user is throwing me above error. Is there any configuration change that needs to be verified for the "shc_uadmin" user as part of procedures? or whether the procedures are created by once user cannot be executed/called by another. What are all the pointers here that need to be checked to resolve this issue? Kindly assist

Regards,

Sasmit Utkarsh

+91-7674022625