Hi Postgres Team,
Kindly assist with the issue faced while calling procedures on postgresql
using libpq in C. I have attached all the details in the note. Please let
me know if you need any more information
Regards,
Sasmit Utkarsh
+91-7674022625
In general code flow:
main() -> SQL_init_db_connection() -> SQL_get_RIAT_size()
globals:
char SelectSizeName[11];
char SelectSizeCommand[150];
int SelectSizeNParams;
Oid SelectSizeParamTypes[2];
//SQL_init_db_connection() which initiates connection and creates procedures
e.t.c
SQL_init_db_connection():
{
//some code
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; $$;");
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR("CREATE sql_select_size_procedure failed! %s",
PQerrorMessage(conn));
SQL_exit_nicely(conn,res);
}
PQclear(res);
// some other code
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}
//some other code
res = PQprepare(conn,
SelectSizeName,
SelectSizeCommand,
SelectSizeNParams,
SelectSizeParamTypes);
LOG_DEBUG("%s() PREPARE SelectSize PQresultStatus =
%s",__func__,PQresStatus(PQresultStatus(res)));
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR("PREPARE failed for RIAT! %s", PQerrorMessage(conn));
SQL_exit_nicely(conn,res);
}
PQclear(res);
}
//But in SQL_get_RIAT_size():
/*-------------------------------------------------------------
return block size from RIAT table for record ID and RTP
--------------------------------------------------------------*/
void SQL_get_RIAT_size(unsigned int ID, int rtp, int *BlkSize)
{
int blkSz = BLOCK_L4; // default to 4k if RIAT table does not contain the
blocksize for the Record ID and rtp
char *size = NULL;
char *rtpVal = (char *)&rtp;
char hexId[9] = {0};
char *hexIdVal = (char *)&hexId;
const char *paramValues[2] = {hexIdVal, rtpVal};
int paramLengths[2] = {4, sizeof(rtp)};
int paramFormats[2] = {1, 1};
int resultFormat = 1;
int nFields = 0;
int nTuples = 0;
PGresult *res = NULL;
sprintf (hexId, "%04X", ID);
LOG_TRACE("%s() - ID %s rtp %i
---------------------------------------",__func__,hexId,rtp);
if(sql_db)
{
LOG_DEBUG("%s() SelectSizeCommand = %s '%s'
%d",__func__,SelectSizeCommand,hexId,rtp);
LOG_DEBUG("%s() SeelctSizeNParams =
%i",__func__,SelectSizeNParams);
res = PQexecPrepared (conn,
SelectSizeName,
SelectSizeNParams,
paramValues,
paramLengths,
paramFormats,
resultFormat);
}
else
{
char queryStmt[100] = {0};
sprintf (queryStmt, "SELECT size FROM riat WHERE id =
'%s' AND rtp = %d", hexId, rtp);
LOG_DEBUG("%s() FILE path - %s",__func__,queryStmt);
PQclear(res);
res = PQexec (conn, queryStmt);
}
LOG_DEBUG("%s() res =
%s",__func__,PQresStatus(PQresultStatus(res)));
if ( PQresultStatus(res) != PGRES_TUPLES_OK ) {
LOG_ERROR("SELECT failed: %s", PQerrorMessage(conn));
PQclear(res);
} else {
nFields = PQnfields(res);
nTuples = PQntuples(res);
LOG_DEBUG("%s() nFields=%i
nTuples=%i",__func__,nFields,nTuples);
if (nFields == 0 || nTuples == 0) {
LOG_ERROR("SELECT failed: NO ROWS/COLUMNS
RETURNED");
PQclear(res);
} else if ( nTuples > 1 ) {
LOG_ERROR("More than 1 row is returned");
PQclear(res);
} else {
size = PQgetvalue (res, 0, 0);
LOG_DEBUG("%s() - size = %s",__func__,size);
//See the below logs size is showing empty, even if there are
records in db
if ( memcmp (size, "Small", 5) == 0 ) {
blkSz = BLOCK_L1;
} else if ( memcmp (size, "Large", 5) == 0 ) {
blkSz = BLOCK_L2;
} else if ( memcmp (size, "4K", 2) == 0) {
blkSz = BLOCK_L4;
}
}
}
*BlkSize = blkSz;
}
Logs:
----
<7>0490218-SQL_init_db_connection() Connection to shc_data database SUCCESSFUL
<6>0490218-[INFO] PostgreSQL Server Version = 1200.16 protocol 3
<7>0490218-SQL_init_db_connection() PREPARE AdvisoryLockData PQresultStatus =
PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE AdvisoryUnLockData PQresultStatus =
PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() CREATE sql_select_size_procedure
PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE SelectSize PQresultStatus =
PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() CREATE sql_select_data_procedure
PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE SelectData PQresultStatus =
PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() CREATE sql_insert_data_procedure
PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE InsertData PQresultStatus =
PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() CREATE sql_update_data_procedure
PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE UpdateData PQresultStatus =
PGRES_COMMAND_OK
<7>0490218-SQL_get_RIAT_size() SelectSizeCommand = CALL
SQL_select_size_procedure($1, $2, NULL) 'C1C1' 4
<7>0490218-SQL_get_RIAT_size() SeelctSizeNParams = 2
<7>0490218-SQL_get_RIAT_size() res = PGRES_TUPLES_OK
<7>0490218-SQL_get_RIAT_size() nFields=1 nTuples=1
<7>0490218-SQL_get_RIAT_size() - size =
//See this line and in DB i could see the record but size is null or empty
DB:
--
[shc_user@cucmtpccu1 ~]$ shc_data
psql (16.1, server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression:
off)
Type "help" for help.
shc_data=> select size from riat where ID='C1C1' and rtp=4;
size
-------
Large
(1 row)
//whereas when rtp is 0, it fetches the correct values in the logs and DB
<7>0002301-SQL_get_RIAT_size() SelectSizeCommand = CALL
SQL_select_size_procedure($1, $2, NULL) 'D6D4' 0
<7>0002301-SQL_get_RIAT_size() SeelctSizeNParams = 2
<7>0002301-SQL_get_RIAT_size() res = PGRES_TUPLES_OK
<7>0002301-SQL_get_RIAT_size() nFields=1 nTuples=1
<7>0002301-SQL_get_RIAT_size() - size = Large //look
this line it fetches the correct value of size
shc_data=> select size from riat where ID='D6D4' and rtp=0;
size
-------
Large
(1 row)
I dnt understand why the procedure only works for rtp=0.. Is something missing
while calling the procedure or some logical errors in the code. Please
assist
