Обсуждение: Clarity regarding the procedures call in postgresql for public and non-public schema

Поиск
Список
Период
Сортировка

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

От
Sasmit Utkarsh
Дата:
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

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

От
Sasmit Utkarsh
Дата:
Hello Boris,

Please find the below snippets for sql_select_size_procedure

/** creation **/
                        res = PQexec(conn," CREATE OR REPLACE PROCEDURE sql_select_size_procedure(hexid text, rtp_in integer, INOUT size_data text
) LANGUAGE plpgsql AS $$ BEGIN SELECT size FROM riat WHERE id = hexid AND rtp = rtp_in INTO size_data; END; $$;");
                        LOG_DEBUG("%s() CREATE sql_select_size_procedure PQresultStatus = %s",__func__,PQresStatus(PQresultStatus(res)));
                        if(PQresultStatus(res) != PGRES_COMMAND_OK)
                        {
                                LOG_ERROR("CREATE sql_select_size_procedure failed! %s", PQerrorMessage(conn));
                                SQL_exit_nicely(conn,res);
                        }

 /**Calling 
sprintf(SelectSizeName,"%s","SelectSize");
 if(SQL_vsn10) {
         sprintf(SelectSizeCommand,"%s","SELECT size FROM riat WHERE id = $1 AND rtp = $2");
 } else {
         sprintf(SelectSizeCommand,"%s","CALL SQL_select_size_procedure($1, $2, NULL)");
 }
 SelectSizeNParams       = 2;
 SelectSizeParamTypes[0] = 25;  // {text}
 SelectSizeParamTypes[1] = 23;  // {int}

The point here I'm trying to make is that the same procedure is called with similar inputs in the earlier mail, But it is getting executed for 1 user i.e "pgddb_admin"(admin user) but not for shc_uadmin. 


Regards,
Sasmit Utkarsh
+91-7674022625


On Mon, Feb 26, 2024 at 6:24 PM Boris Zentner <bzm@2bz.de> wrote:
You call the function with null as last argument. 
(SQL_select_size_procedure)But the function expect text. Either provide text or cast the null  like null::text. Or change the function input. 
--
Boris


Am 26.02.2024 um 13:27 schrieb Sasmit Utkarsh <utkarshsasmit@gmail.com>:

SQL_select_size_procedure

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

От
Adrian Klaver
Дата:
On 2/26/24 04:26, Sasmit Utkarsh wrote:
> Hi Postgresql Team,
> 
> ***Facing the below issue, while calling the below procedures in public 
> and non-public schema *shc* for ( *shc_uadmin* user)
> 

> *<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

1) From the error message:

"ERROR:  procedure sql_select_size_procedure(text,
integer, unknown) does not exist"

"No procedure matches the given name and argument types"

 From the call:

CALL SQL_select_size_procedure($1, $2, NULL)

You might try:

CALL SQL_select_size_procedure($1, $2, NULL::text)

2) Any clue as to where this:

"...failed for RIAT..."

is comimg from?



-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

От
Tom Lane
Дата:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 2/26/24 04:26, Sasmit Utkarsh wrote:
>> ***Facing the below issue, while calling the below procedures in public 
>> and non-public schema *shc* for ( *shc_uadmin* user)

> 1) From the error message:

> "ERROR:  procedure sql_select_size_procedure(text,
> integer, unknown) does not exist"
> "No procedure matches the given name and argument types"

This perhaps would be better understood as "there is no matching
procedure in the current search_path".  The fact that it works as
one user and not as another could be explained if the users have
different search paths.

            regards, tom lane