Re: Stess test via libpq for postgreSQL DB
Thanks David Regards, Sasmit Utkarsh +91-7674022625 On Sat, Apr 27, 2024 at 11:44 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sat, Apr 27, 2024 at 11:07 AM Sasmit Utkarsh > wrote: > >> >> But i have some clarifications if we can use it to execute PLSQL code >> blocks rather than standalone SQL queries. >> > > https://www.postgresql.org/docs/current/sql-do.html > > David J. > >
Stess test via libpq for postgreSQL DB
Hi Postgresql Team, Could you please help on how to get started with some stress test for my application which uses libpq to interact with postgres flexi db. I tried to explore pgbench which is a builtin tool to get some stats. But i have some clarifications if we can use it to execute PLSQL code blocks rather than standalone SQL queries. Please advise Regards, Sasmit Utkarsh +91-7674022625
Re: Need some assistance on stored procedures execution using libpq in C
Hi Pavel, Thanks for the info. But is it not possible to have some kind of handling of an empty result set using libpq for the given procedure? Regards, Sasmit Utkarsh +91-7674022625 On Thu, Apr 25, 2024 at 8:26 PM Pavel Stehule wrote: > Hi > > čt 25. 4. 2024 v 12:57 odesílatel Sasmit Utkarsh > napsal: > >> Hi PostgreSQL Team, >> >> I'm trying to execute the stored procedure(details along with the program >> in the attachment) to fetch the records from the table for the inputs given >> in the code as well. I have already created the procedure in the db. But I >> see some strange output when I try to fetch the details from the table >> using *PQexecPrepared*, even though there are no records for the given >> input. I see that this message nTuples(rows)=1 along with procedure >> successful execution. How to better handle this? >> when it returned probably an empty result set. Please advise >> > > PQ interface is designed for client server communication > > you should to use SPI API > > https://www.postgresql.org/docs/current/spi.html > > Regards > > Pavel > > >> >> *Output:* >> >> >> >> >> >> *sasmit@sasmit-Virtual-Machine:~/test$ ./test_proceduresmain() Connection >> to shadow_shc_data database SUCCESSFULmain() nFields(cols)=2 >> nTuples(rows)=1SELECT operation succeeded on Shadow DBmain() blk_size >> returned is 7565871* >> >> *DB:* >> >> >> >> >> >> >> >> >> >> >> *[nix-shell:/ext/shb/tpfasm/shares-c]$ psql shadow_shc_datapsql (14.9, >> server 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1))Type "help" for >> help.shadow_shc_data=# select * from fs_data;shadow_shc_data=# CALL >> SQL_select_data_procedure(-335509949,false , NULL, NULL); blksize | >> fadata-+ |(1 row)* >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> *shadow_shc_data-# \dfList of functions-[ RECORD 1 >> ]---+-Schema >> | publicName| sql_insert_data_procedureResult >> data type|Argument data types | fa integer, ft integer, ord integer, >> xaddr text, recid text, blk_size integer, indata bytea, INOUT outdata >> byteaType| proc-[ RECORD 2 >> ]---+-Schema >> | publicName| sql_select_data_procedureResult >> data type|Argument data types | fa integer, hold boolean, INOUT blksize >> integer, INOUT fadata byteaType| proc-[ RECORD 3 >> ]---+-Schema >> | publicName| sql_update_data_procedureResult >> data type|Argument data types | indata bytea, unhold boolean, fa >> integerType| proc* >> >> Regards, >> Sasmit Utkarsh >> +91-7674022625 >> >
Need some assistance on stored procedures execution using libpq in C
Hi PostgreSQL Team,
I'm trying to execute the stored procedure(details along with the program
in the attachment) to fetch the records from the table for the inputs given
in the code as well. I have already created the procedure in the db. But I
see some strange output when I try to fetch the details from the table
using *PQexecPrepared*, even though there are no records for the given
input. I see that this message nTuples(rows)=1 along with procedure
successful execution. How to better handle this?
when it returned probably an empty result set. Please advise
*Output:*
*sasmit@sasmit-Virtual-Machine:~/test$ ./test_proceduresmain() Connection
to shadow_shc_data database SUCCESSFULmain() nFields(cols)=2
nTuples(rows)=1SELECT operation succeeded on Shadow DBmain() blk_size
returned is 7565871*
*DB:*
*[nix-shell:/ext/shb/tpfasm/shares-c]$ psql shadow_shc_datapsql (14.9,
server 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1))Type "help" for
help.shadow_shc_data=# select * from fs_data;shadow_shc_data=# CALL
SQL_select_data_procedure(-335509949,false , NULL, NULL); blksize |
fadata-+ |(1 row)*
*shadow_shc_data-# \dfList of functions-[ RECORD 1
]---+-Schema
| publicName| sql_insert_data_procedureResult
data type|Argument data types | fa integer, ft integer, ord integer,
xaddr text, recid text, blk_size integer, indata bytea, INOUT outdata
byteaType| proc-[ RECORD 2
]---+-Schema
| publicName| sql_select_data_procedureResult
data type|Argument data types | fa integer, hold boolean, INOUT blksize
integer, INOUT fadata byteaType| proc-[ RECORD 3
]---+-Schema
| publicName| sql_update_data_procedureResult
data type|Argument data types | indata bytea, unhold boolean, fa
integerType| proc*
Regards,
Sasmit Utkarsh
+91-7674022625
/*** stored Procedure:
CREATE OR REPLACE PROCEDURE sql_select_data_procedure(
fa integer,
hold bool,
INOUT blksize integer,
INOUT fadata bytea
) LANGUAGE plpgsql AS
$$
BEGIN
IF (hold) THEN
PERFORM pg_advisory_lock(fa);
END IF;
SELECT blk_size, data INTO blksize, fadata
FROM fs_data
WHERE file_address = fa;
END;
$$;
***/
#include
#include
#include
#include
#include // For htonl
int main() {
int nFields = 0;
int nTuples = 0;
int blk_size = 0;
PGconn *shadow_db_conn;
PGresult *res;
const char *shadow_db_conn_info = "dbname=shadow_shc_data";
int is_shadow_db = 1; // Assuming it's a shadow database
char *blk_size_val;
char *data_val;
// Connect to the shadow database
if (is_shadow_db) {
shadow_db_conn = PQconnectdb(shadow_db_conn_info);
if (PQstatus(shadow_db_conn) != CONNECTION_OK) {
fprintf(stderr, "Connection to shadow database failed: %s\n",
PQerrorMessage(shadow_db_conn));
PQfinish(shadow_db_conn);
exit(1);
}
// Set bytea_output to 'escape'
res = PQexec(shadow_db_conn, "SET bytea_output = 'escape'");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr, "SET bytea_output command failed: %s\n",
PQerrorMessage(shadow_db_conn));
PQclear(res);
PQfinish(shadow_db_conn);
exit(1);
}
PQclear(res);
printf("%s() Connection to shadow_shc_data database SUCCESSFUL\n",
__func__);
}
// Construct the command to prepare
char SelectDataName[11];
char SelectDataCommand[150];
int SelectDataNParams;
Oid SelectDataParamTypes[2];
sprintf(SelectDataName,"%s","SelectData");
sprintf(SelectDataCommand, "CALL SQL_select_data_procedure($1, $2, NULL,
NULL)");
SelectDataNParams = 2;
SelectDataParamTypes[0] = 23; // int
SelectDataParamTypes[1] = 16; // bool
// Prepare the statement
if (is_shadow_db) {
res = PQprepare(shadow_db_conn,
SelectDataName,
SelectDataCommand,
SelectDataNParams,
SelectDataParamTypes);
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr, "Prepare failed: %s\n",
PQerrorMessage(shadow_db_conn));
PQclear(res);
PQfinish(shadow_db_conn);
exit(1);
}
PQclear(res);
}
// Sample input values
int32_t fa = -335509949
Re: Assistance needed for the query execution in non-public schema
Thanks Laurenz and David
Regards,
Sasmit Utkarsh
+91-7674022625
On Tue, 16 Apr, 2024, 16:58 Laurenz Albe, wrote:
> On Tue, 2024-04-16 at 16:30 +0530, Sasmit Utkarsh wrote:
> > msshctd=> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr', 'id'),
> coalesce(MAX(id), 1)) from mqa_flfo_cstr;
> > ERROR: permission denied for sequence mqa_flfo_cstr_id_seq
> > msshctd=> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval', 'id'),
> coalesce(MAX(id), 1)) from mqa_ffp_nval;
> > ERROR: permission denied for sequence mqa_ffp_nval_id_seq
> >
> > msshctd=> \z mqa_flfo_cstr_id_seq
> >Access privileges
> > Schema | Name | Type | Access privileges
> | Column privileges | Policies
> >
> -+--+--+-+---+--
> > shc_tty | mqa_flfo_cstr_id_seq | sequence |
> pgddb_admin=rwU/pgddb_admin+| |
> > | | | shc_uadmin=rU/pgddb_admin
> | |
> > (1 row)
> >
> > msshctd=> \z mqa_ffp_nval_id_seq
> >Access privileges
> > Schema |Name | Type | Access privileges
> | Column privileges | Policies
> >
> -+-+--+-+---+--
> > shc_tty | mqa_ffp_nval_id_seq | sequence |
> pgddb_admin=rwU/pgddb_admin+| |
> > | | | shc_uadmin=rU/pgddb_admin
> | |
> > (1 row)
> >
> > Above query executes fine when I try to execute as user
> "pgddb_admin"(Super User).
> > but when I switch to shc_uadmin, I see a permission error.
>
> That's because the user is lacking the UPDATE privilege ("U" is USAGE).
>
> > GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin;
> > ERROR: unrecognized privilege type "alter" SQL state: 42601
>
> There is no ALTER privilege. Try
>
> GRANT UPDATE ON SEQUENCE mqa_flfo_cstr_id_seq, mqa_ffp_nval_id_seq TO
> shc_uadmin;
>
> Yours,
> Laurenz Albe
>
Assistance needed for the query execution in non-public schema
Hi Postgresql Team,
I'm trying to execute the below query using the below
psql 'host=cucmpsgsu0.postgres.database.azure.com port=5432 dbname=msshctd
user=shc_uadmin password=x sslmode=require
options=--search_path=shc_tty'
msshctd=> show search_path;
search_path
-
shc_tty
(1 row)
msshctd=> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr', 'id'),
coalesce(MAX(id), 1)) from mqa_flfo_cstr;
*ERROR: permission denied for sequence mqa_flfo_cstr_id_seq*
msshctd=> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval', 'id'),
coalesce(MAX(id), 1)) from mqa_ffp_nval;
*ERROR: permission denied for sequence mqa_ffp_nval_id_seq*
msshctd=> \z mqa_flfo_cstr_id_seq
Access privileges
Schema | Name | Type | Access privileges |
Column privileges | Policies
-+--+--+-+---+--
shc_tty | mqa_flfo_cstr_id_seq | sequence | pgddb_admin=rwU/pgddb_admin+|
|
| | | *shc_uadmin=rU/pgddb_admin*
| |
(1 row)
msshctd=> \z mqa_ffp_nval_id_seq
Access privileges
Schema |Name | Type | Access privileges |
Column privileges | Policies
-+-+--+-+---+--
shc_tty | mqa_ffp_nval_id_seq | sequence | pgddb_admin=rwU/pgddb_admin+|
|
| | | *shc_uadmin=rU/pgddb_admin * |
|
(1 row)
Above query executes fine when I try to execute as user *"pgddb_admin"(Super
User)*. but when I switch to shc_uadmin, I see a permission error. Above
I've included the permissions listed for both the users. I also tried to
set the alter permissions for one of the sequence for the shc_uadmin user
with the below
GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin;
*ERROR: unrecognized privilege type "alter" SQL state: 42601*
Is there any other way i can execute the queries, with user *"shc_uadmin"*
for the *setval()* to work properly without giving complete privileges same
as the owner *"pgddb_admin"* ? Any workaround/ alternate option which would
work here? Please assist
Regards,
Sasmit Utkarsh
+91-7674022625
Re: Need Assistance: Command to display procedures does not work
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
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
> > mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
Re: Need Assistance: Command to display procedures does not work
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
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.kla...@aklaver.com
>
>
Re: Need Assistance: Command to display procedures does not work
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 wrote:
>
>
> > On Feb 28, 2024, at 8:53 AM, 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.
> >
> > [sutkars...@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.
>
>
>
Re: Need Assistance: Command to display procedures does not work
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
wrote:
> 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.
> >
> > [sutkars...@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.kla...@aklaver.com
>
>
Need Assistance: Command to display procedures does not work
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.
[sutkars...@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.
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 existLINE 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
Re: Clarity regarding the procedures call in postgresql for public and non-public schema
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 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 :
>
> SQL_select_size_procedure
>
>
Clarity regarding the procedures call in postgresql for public and non-public schema
e* | | 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: Postgresql assistance needed
Thanks, I'll check it out. Regards, Sasmit Utkarsh +91-7674022625 On Thu, 22 Feb, 2024, 21:40 Laurenz Albe, wrote: > On Thu, 2024-02-22 at 19:35 +0530, Sasmit Utkarsh wrote: > > Is there any configuration/query that can be checked to verify if > "shc_uadmin" has the correct path set? > > The SQL statement "SHOW search_path" would return the current setting. > > But look at the error message first. > > Yours, > Laurenz Albe > >
Re: Postgresql assistance needed
Thanks Laurenz
->To debug that, get the actual error message using PQerrorMessage().
That should tell you what is going on. -- will try to add the
PQerrorMessage() in the logs
->Perhaps the sequence is not on your "search_path", and you should
qualify the name with the schema. -- I have given the below set of env
variables including setting the schema path, which works well for
*"pgddb_admin"
*and not for user "*shc_uadmin". *Is there any configuration/query that
can be checked to verify if "*shc_uadmin" *has the correct path set?
export PGHOST=cucmpsgsu0.postgres.database.azure.com
export PGDATABASE=mshcd
*export PGUSER=shc_uadmin*
export PGPASSWORD=abc123
export PGOPTIONS='--search_path=*shc,shc_tty,public*'
Regards,
Sasmit Utkarsh
+91-7674022625
On Thu, Feb 22, 2024 at 7:11 PM Laurenz Albe
wrote:
> On Thu, 2024-02-22 at 17:18 +0530, Sasmit Utkarsh wrote:
> > Perhaps, the issue I wanted to highlight here is that I get the same
> entry working
> > when I switch user to "pgddb_admin" and not when change user with same
> privileges
> > as PGUSER "shc_uadmin" I get the message in the error log like
> > "<3>3343433-[ERROR] SELECT nextval Failed [../tpfasm.c:3561:get_pool_fa]"
> > even though i see the next val from db. Please see below when logged in
> to the db
> > and snippet of the code lines where it was throwing the error.
> >
> > [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,public"
> > psql (16.1, server 15.4)
> > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> compression: off)
> > Type "help" for help.
> > mshcd=> SELECT nextval('unassigned_pool_fa');
> >nextval
> > -
> > -1811939322
> > (1 row)
> >
> >
> > code snippet:
> > 3555 } else {
> > 3556 LOG_WARN("No File_address is found with
> status=free");
> > 3557 //If no free address is found, get the next
> value available address from the sequence
> > 3558 PQclear(res);
> > 3559 res = PQexec(conn, "SELECT
> nextval('unassigned_pool_fa');");
> > 3560 if (PQresultStatus(res) != PGRES_TUPLES_OK)
> {
> > 3561 LOG_ERROR("SELECT nextval Failed");
> > 3562 LOG_DEBUG("ROLLBACK TRANSACTION");
> > 3563 res = PQexec(conn,"ROLLBACK
> TRANSACTION");
> > 3564 PQclear(res);
> > 3565 return 0;
> > 3566 }
>
> To debug that, get the actual error message using PQerrorMessage().
> That should tell you what is going on.
>
> Perhaps the sequence is not on your "search_path", and you should
> qualify the name with the schema.
>
> Yours,
> Laurenz Albe
>
Re: Postgresql assistance needed
Hi Laurenz/Postgresql Team,
Perhaps, the issue I wanted to highlight here is that I get the same entry
working when I switch user to *"pgddb_admin" *and not when change user
with same privileges as PGUSER "*shc_uadmin" *I get the message in the
error log like* "<3>3343433-[ERROR] SELECT nextval Failed
[../tpfasm.c:3561:get_pool_fa]" *even though i see the next val from db.
Please see below when logged in to the db and snippet of the code lines
where it was throwing the error.
*[shc_user@cucmtpccu1 ~]$ psql
"host= cucmpsgsu0.postgres.database.azure.com
<http://cucmpsgsu0.postgres.database.azure.com/> port=5432 dbname=mshcd
user=shc_uadmin password=abc123 sslmode=require
options=--search_path=shc,public"*
*psql (16.1, server 15.4)SSL connection (protocol: TLSv1.3, cipher:
TLS_AES_256_GCM_SHA384, compression: off)Type "help" for help.*
*mshcd=> SELECT nextval('unassigned_pool_fa');
nextval- -1811939322(1 row)*
code snippet:
3555 } else {
3556 LOG_WARN("No File_address is found with
status=free");
3557 //If no free address is found, get the next
value available address from the sequence
3558 PQclear(res);
*3559 res = PQexec(conn, "SELECT
nextval('unassigned_pool_fa');");*
3560 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
3561 LOG_ERROR("SELECT nextval Failed");
3562 LOG_DEBUG("ROLLBACK TRANSACTION");
3563 res = PQexec(conn,"ROLLBACK
TRANSACTION");
3564 PQclear(res);
3565 return 0;
3566 }
Is the issue related to configuration of PGUSER or something else
Regards,
Sasmit Utkarsh
+91-7674022625
On Thu, Feb 22, 2024 at 4:20 PM Laurenz Albe
wrote:
> On Thu, 2024-02-22 at 16:13 +0530, Sasmit Utkarsh wrote:
> > On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe
> wrote:
> > > On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote:
> > > > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address
> 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp 0xff8a70b0
> > > > READ of size 4096 at 0xf337ba80 thread T0
> > > > #0 0xf795fcdc in __interceptor_memcpy
> (/nix/store/3blqv6wzmwqgkkw45i6c8w0bkwldk418-gcc-12.2.0-lib/lib/libasan.so.8+0x42cdc)
> > > > #1 0xf78c34bb in pqPutnchar
> (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x174bb)
> > > > #2 0xf78be05e in PQsendQueryGuts
> (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e)
> > > > #3 0xf78c05a2 in PQexecPrepared
> (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2)
> > >
> > > Perhaps you forgot to terminate a string with '\0'.
> >
> > Sorry but are you talking about the export variables in the result?
>
> Whatever you fed to PQexecPrepared.
>
> Yours,
> Laurenz Albe
>
[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=3mnAK~lc7
[shc_user@cucmtpccu1 ~]$ export PGOPTIONS='--search_path=shc,shc_tty,public'
[shc_user@cucmtpccu1 ~]$ export PGUSER=pgddb_admin
[shc_user@cucmtpccu1 ~]$ 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_connecti
Re: Postgresql assistance needed
Hi Laurenz, Sorry but are you talking about the export variables in the result? Regards, Sasmit Utkarsh +91-7674022625 On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe wrote: > On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote: > > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address > 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp 0xff8a70b0 > > READ of size 4096 at 0xf337ba80 thread T0 > > #0 0xf795fcdc in __interceptor_memcpy > (/nix/store/3blqv6wzmwqgkkw45i6c8w0bkwldk418-gcc-12.2.0-lib/lib/libasan.so.8+0x42cdc) > > #1 0xf78c34bb in pqPutnchar > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x174bb) > > #2 0xf78be05e in PQsendQueryGuts > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e) > > #3 0xf78c05a2 in PQexecPrepared > (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2) > > Perhaps you forgot to terminate a string with '\0'. > > Yours, > Laurenz Albe >
Postgresql assistance needed
Hi Postgresql Team,
I'm facing a weird issue which testing the application code using libpq in
C. when i run a test case with PGUSER as *"pgddb_admin" * i get the
expected result (more details attached in success_log doc) whereas when i
run the same test case using another user "*shc_uadmin" *which has same
privileges, I get below error message highlighted(more details in Error_log
doc attached)
*<3>3343433-[ERROR] SELECT nextval Failed [../tpfasm.c:3561:get_pool_fa*]
More details related to "*shc_uadmin"* and sequence *"unassigned_pool_fa"*
which is present in non-public schema *(shc)* below from the terminal.
*[shc_user@cucmtpccu1 ~]$ psql "host=
cucmpsgsu0.postgres.database.azure.com
<http://cucmpsgsu0.postgres.database.azure.com> port=5432 dbname=mshcd
user=shc_uadmin password=abc123 sslmode=require
options=--search_path=shc,public"psql (16.1, server 15.4)SSL connection
(protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)Type
"help" for help.mshcd=> \dp unassigned_pool_fa
Access privileges Schema |Name| Type |
Access privileges | Column privileges |
Policies++--+-+---+--
shc
| unassigned_pool_fa | sequence | pgddb_admin=rwU/pgddb_admin+|
||| |
shc_uadmin=rwU/pgddb_admin | |(1 row)mshcd=> SELECT
nextval('unassigned_pool_fa'); nextval- -1811939322(1 row)*
Is there any other issue related to the permissions or configuration
associated with the *shc_uadmin* user in PostgreSQL which needs to be
checked/verified? Please assist with your inputs
Regards,
Sasmit Utkarsh
+91-7674022625
[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=3mnAK~lc7
[shc_user@cucmtpccu1 ~]$ export PGOPTIONS='--search_path=shc,shc_tty,public'
[shc_user@cucmtpccu1 ~]$ export PGUSER=pgddb_admin
[shc_user@cucmtpccu1 ~]$ 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
<7>3429958-loading builtin FACE
<7>3429958-loading builtin FMSG
<7>3429958-loading builtin FMLG
<7>3429958-loading builtin UII1
<7>3429958-loading builtin UIO1
<7>3429958-loading builtin UIM1
<7>3429958-loading builtin UIY3
<7>3429958-loading builtin UIY2
<7>3429958-loading builtin UIY1
<7>3429958-loading builtin UIZ1
<7>3429958-loading builtin WGA1
<7>3429958-loading builtin COPA
<7>3429958-loading builtin HCVT
<7>3429958-loading builtin HTEA
<7>3429958-loading builtin WGR1
<7>3429958-loading builtin UGK6
<7>3429958-loading builtin UGK7
<7>3429958-loading builtin CVFG
<7>3429958-loading builtin GMI9
<7>3429958-loading builtin HLOG
<7>3429958-loading builtin GMI8
<7>3429958-loading builtin UMA1
<7>3429958-loading builtin UMD1
<7>3429958-loading builtin UMS1
<7>3429958-loading builtin UGI1
<7>3429958-loading builtin HTCZ
<7>3429958-loading builtin C000
<7>3429958-loading builtin HRD4
<7>3429958-loading builtin HTCY
<4>3429958-[WARN] init_globals() not fully unimplemented
<7>3429958-init_globals() load_core_resident
<4>3429958-[WARN] Init dbref_SW05SR
<4>3429958-[WARN] Init dbref_SR05SR
<4>3429958-[WARN] Init dbref_GW01SR
<4>3429958-[WARN] Init dbref_SEQ0SW
<4>3429958-[WARN] Init dbref_FFQ0SW
<4>342
Re: Clarification regarding managing advisory locks in postgresql
Okay Thanks. Also please help me understand the below scenarios >From the above statement, I understand is (please correct if I'm wrong here), When we fork a client process, each process gets its own database connection or transaction context. Therefore, locks acquired in one process (or transaction) do not directly affect locks in another process (or transaction). Now, I'm faced with another situation where I'm using libpq in C as client programs and while calling some function it acquires pg_advisory_lock for the request with some identifier in transaction A. This can be thought of as “lock the operation with id = X” and then make some SQL requests(retrieve) from the database. During that if it forks into another process B, If any other transaction, e.g. B, will attempt to acquire lock with the same id, then do some update operation on DB for the id=X then will it wait until such lock will be released by A? what would be status of the lock in process A and B? which process will have the lock? If they are independent of each other then how can we maintain synchronization between the processes to access the locks properly, when they are forked Please let me know if you need any more details or clarfications Regards, Sasmit Utkarsh +91-7674022625 On Thu, Jan 25, 2024 at 10:42 AM Christophe Pettus wrote: > > > > On Jan 24, 2024, at 19:17, Sasmit Utkarsh > wrote: > > > > Need your support on understanding advisory locks in Postgresql and what > is the best practice to have advisory locks and unlocks to work properly > when we have multiple process forked from single process? > > Advisory locks are a shared resource across all of the database in which > they were created, and PostgreSQL isn't aware of the process model that the > client is using. If a session creates the advisory lock, that same session > will need to be the one to release it, and it's up to the client program to > keep track of which process is using which session.
Clarification regarding managing advisory locks in postgresql
Hi Postgresql Team, Need your support on understanding advisory locks in Postgresql and what is the best practice to have advisory locks and unlocks to work properly when we have multiple process forked from single process? is there any option for setting the locktype or please share a sample program using libpq in C explaining the above scenario if possible Regards, Sasmit Utkarsh +91-7674022625
Re: Need assistance for running postgresql procedures
Thanks, I'll check it out. Regards, Sasmit Utkarsh +91-7674022625 On Wed, 24 Jan, 2024, 01:59 Francisco Olarte, wrote: > Hi sasmit. > > On Tue, 23 Jan 2024 at 18:51, Sasmit Utkarsh > wrote: > > 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 > > I'm not familiar with using binary parameters, but are you sured you > do not need calling htonl(3) on the rtp value? ( 3 is man section, not > htonl argument ). > > "Values passed in binary format require knowledge of the internal > representation expected by the backend. > * For example, integers must be passed in network byte order. > ** > Passing numeric values requires knowledge of the server storage > format, as implemented in > src/backend/utils/adt/numeric.c::numeric_send() and > src/backend/utils/adt/numeric.c::numeric_recv()." > > A quick test would be to use rtp=0x0400 and see if it then works ( > if it comes from cmd line or similar, if it comes from code just use > hton ) > > > Francisco Olarte. >
Need assistance for running postgresql procedures
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,
Re: Help needed for the resolution of memory leak
ok Thanks Merlin, I will go through the above specified doc and get back in case of further questions Regards, Sasmit Utkarsh +91-7674022625 On Thu, Jan 18, 2024 at 6:36 AM Merlin Moncure wrote: > On Wed, Jan 17, 2024 at 1:14 PM Sasmit Utkarsh > wrote: > >> Hi Merlin et al. >> >> Below are some couple of observations attached as an "overview_of_code" >> and other attachments "function_def_other_details" and leak sanitizer >> report. Please assist with some clarifications given in overview_of_code >> with (***). Let me know if you need any more information >> > > ***How do we handle for the case clearing when PGresult object is assigned a > pointer to the data of the specified field within the existing PGresult > object? > i.e when SQL_get_tpf_rw() actually completes in each iteration? > > > > It is your responsibility to close PGResult and PGConn objects. Each one > created must be cleaned up. This is basic libpq usage. I suggest studying > the documentation. > > > > Start here: https://www.postgresql.org/docs/current/libpq-exec.html > > Also Study here: https://www.postgresql.org/docs/current/libpq-example.html > > > You should not reuse a pointer unless you have cleared the object first. > > > Is the leak reported due to improper handling of the above case ? or is > it due to some other flow > > Your leaks look mostly due to not cleaning PGResult. However, the real issue > here is you need to learn basic libpq usage a little better...try writing a > smaller program and see when it starts to complain about leaks. > > > merlin > >
Re: Help needed for the resolution of memory leak
Thanks, I'll take a look.
Regards,
Sasmit Utkarsh
+91-7674022625
On Wed, Jan 17, 2024 at 4:12 AM Merlin Moncure wrote:
> On Tue, Jan 16, 2024 at 9:10 AM Sasmit Utkarsh
> wrote:
>
>> Hi Merlin et al.
>>
>> I have tried to have the above change added in the missing places. Still,
>> I see the below leaks reported by the address sanitizer. Please see
>> the attachments for the leak reported and the function definition updated.
>> Not sure for PQexecPrepared if we call PQclear(res) for cleaning up as
>> well. let me know if you need any more information
>>
>
> You have many other leaks.See code below, you are making up to thee
> sequential calls to create a result before calling clear. *All *calls
> creating and returning returning PGresult have to be cleared before the
> pointer is reused.
>
> merlin
>
>
>
> LOG_DEBUG("%s() conninfo=%s",__func__,conninfo);
>
> if(is_shadow_db)
> {
> shadow_db_conn = PQconnectdb(shadow_db_conn_info);
> if ( PQstatus(shadow_db_conn ) != CONNECTION_OK )
> {
> LOG_ERROR("Connection to shadow database failed! %s",
> PQerrorMessage(conn));
> PQfinish(shadow_db_conn);
> exit(1);
> }
> *res *= PQexec(shadow_db_conn, "SET bytea_output = 'escape'");
> LOG_DEBUG("%s() Connection to shadow_shc_data database
> SUCCESSFUL",__func__);
> // execute_stored_procedure(shadow_db_conn);
> }
>
> conn = PQconnectdb(conninfo);
> if ( PQstatus(conn) != CONNECTION_OK ) {
> LOG_ERROR("Connection to database failed! %s",
> PQerrorMessage(conn));
> PQfinish(conn);
> exit(1);
> } else {
> *res =* PQexec(conn, "SET bytea_output = 'escape'");
> LOG_DEBUG("%s() Connection to shc_data database
> SUCCESSFUL",__func__);
> }
>
> *res *= PQexec(conn, "START TRANSACTION");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> {
> LOG_ERROR("START TRANSACTION failed: %s", PQerrorMessage(conn));
> SQL_exit_nicely(conn,res);
> }
> PQclear(res);
>
>
Re: Assitance needed for the resolution of memory leak
Thanks Tom, It did work, and yeah I do have indentation in my editor, there was some formatting issue due to the copy and paste of the code. :) Regards, Sasmit Utkarsh +91-7674022625 On Thu, Oct 12, 2023 at 2:07 AM Tom Lane wrote: > Sasmit Utkarsh writes: > > Like for an ex: I have provided the test run for a file which has 4 > > duplicate entries in the input file and below is the leak reported. I > have > > followed > > consistently using PQclear to free all PGresult objects. > > No you haven't: you've got two PQexec-something calls and > only one PQclear. If control reaches here: > > > res = PQexec(conn, Command); > > that will overwrite your only pointer to the PQexecParams > result, and you won't clear it. > > BTW, if this is what your code actually looks like in your editor, > it's no wonder you can't follow its basic control flow. Indent > according to the brace structure, and your life will get easier. > > regards, tom lane >
Re: Assitance needed for the resolution of memory leak
e of res
if ( ( PQresultStatus(res) == PGRES_TUPLES_OK)
&& (PQntuples(res) == 0) ) // if tuples == 0, we hit the ON CONFLICT, so
report the ERROR
{
rc = 1;
fprintf(stderr, "\nERROR: DUPLICATE file address %s\n", xaddr);
fprintf(stderr, "FAIL : INSERT into fs_data file_address = %-11i (%s) recid
= %s face_type = %i ordinal = %i\n", fa,xaddr,recid,ft,ord);
rc2 = SQL_SELECT_recid(fa, conn, cur_recid);
if(rc2 == 0)
{
sprintf(Command,"SELECT face_type, ordinal from fs_data where
file_address=%i",fa);
//fprintf(stderr,"Command = %s\n",Command);
res = PQexec(conn, Command);
if (PQresultStatus(res) == PGRES_TUPLES_OK)
{
int num_rows = PQntuples(res);
if (num_rows > 0)
{
ft3 = atoi(PQgetvalue(res, 0, 0));
ord3= atoi(PQgetvalue(res, 0, 1));
fprintf(stderr, "CURRENT data: in fs_data file_address = %-11i (%s) recid
= %s face_type = %i ordinal = %i\n", fa,xaddr,cur_recid,ft3,ord3);
}
else
{
fprintf(stderr,"FAIL postgreSQL_insert: No rows returned for
file_address=%i\n",fa);
}
}
else
fprintf(stderr,"FAIL postgreSQL_insert: Query execution FAILED: %s\n",
PQresultErrorMessage(res));
PQclear(res);
}
}
else if ( PQresultStatus(res) != PGRES_TUPLES_OK) // something else went
wrong
{
rc = 1;
fprintf(stderr,"ERROR: %s\n",PQresultErrorMessage(res));
PQclear(res);
}
else
{
rc = 0;
if ( sql_debug ) { fprintf(stderr,"INSERT SUCCESSFUL\n"); }
PQclear(res);
}
if ( sql_debug1 ) { printPostgreSQL_SELECT_results(fa,conn); }
return rc;
}
Regards,
Sasmit Utkarsh
+91-7674022625
On Thu, Oct 12, 2023 at 1:06 AM Adrian Klaver
wrote:
> On 10/11/23 12:15, Sasmit Utkarsh wrote:
> > Hi Team,
> >
> > I'm trying to insert multiple duplicate rows into the table using libpq
> > in C. I'm getting a memory leak reported from addresssanitizer tool.
> > Please let me know if i can share the code snippet
>
> That is the only way you are going to get an answer, so share away.
>
> Also include the report information from addresssanitizer.
>
> >
> >
> > Regards,
> > Sasmit Utkarsh
> > +91-7674022625
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
Assitance needed for the resolution of memory leak
Hi Team, I'm trying to insert multiple duplicate rows into the table using libpq in C. I'm getting a memory leak reported from addresssanitizer tool. Please let me know if i can share the code snippet Regards, Sasmit Utkarsh +91-7674022625
