Re: Postgresql assistance needed

[Sasmit Utkarsh <utkarshsasmit@xxxxxxxxx>]

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 <laurenz.albe@xxxxxxxxxxx> 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