Обсуждение: Issue with PQdescribePortal to describe a select cursor

"Brijesh Shrivastav" <Bshrivastav@esri.com> writes:
> Is there any known issue with using PQdescribePortal() against
> a declared select server side cursor?

Should work.  Can you provide a self-contained example of your problem?
        regards, tom lane

Tom,

Please see the test program below that creates a table and then
queries it using DECLARE CURSOR statement. It only describes the
cursor and doesn't really fetch any data. It appears to me I
may be forced to execute the query before describe that will
not be possible for me in our current application (in many
cases I need input bind parameter data from client application
before executing the query).

Thanks,
Brijesh


#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>

#  include <process.h>
#  include <windows.h>
#include "libpq-fe.h"

#define TEST_TABLE  "pgtest"
#define SE_SUCCESS  0
#define EOS  '\0'

//Prototypes

LONG createTable(PGconn *conn);
LONG queryTable(PGconn *conn);

int main(int argc, char* argv[])
{
 char         host[32],database[32],              user[32],password[32],errmsg[256]; int          rc; PGconn
*conn;
 if (argc < 5)   printf("incorrect arguments: provide <server> <database> <user> <password>  \n"); else {
strcpy(host,argv[1]);  strcpy(database,argv[2]);   strcpy(user,argv[3]);   strcpy(password,argv[4]); } 
  conn = PQsetdbLogin (host,                                     NULL,                                     NULL,
                            NULL,                                     database,
user,                                    password); 
 /* Check to see that the  connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) {   fprintf(stderr,
"Connectionto postgres failed.\n");   sprintf(errmsg,"%s",PQerrorMessage(conn));   fprintf(stderr,"ERRMSG:
%s\n",errmsg);} 
 //Create test table rc = createTable(conn); if (rc != SE_SUCCESS) {   fprintf(stderr, "Create Table Failed(%d)\n",rc);
  PQfinish(conn);   exit(1); }    //Query test table rc =  queryTable(conn); if (rc != SE_SUCCESS) {   fprintf(stderr,
"QueryTable Failed(%d):\n",rc);    PQfinish(conn);   exit(1); }  
 PQfinish(conn);

}

LONG createTable(PGconn *conn)
{
 char    sql[512],*msg; PGresult       *res;
 //Drop table if one already exists
 sprintf(sql," DROP TABLE %s ", TEST_TABLE);
 res = PQexec(conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) {       msg = PQresultErrorMessage(res);
if(msg != NULL && msg[0] != EOS  )               fprintf(stderr,"Error dropping table: %s\n",msg); 
 }
 sprintf(sql," CREATE TABLE %s (shortcol smallint, intcol INTEGER, int64col bigint, realcol real,  "   " doublecol
DOUBLEPRECISION, strcol VARCHAR(256),  blobcol BYTEA, timecol timestamp)", TEST_TABLE); 
 res = PQexec(conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) {       msg = PQresultErrorMessage(res);
if(msg != NULL && msg[0] != EOS  )               fprintf(stderr,"Error dropping table: %s\n",msg);       return(-1); 
 } else     printf("Successfully created %s table.\n",TEST_TABLE);
 return(SE_SUCCESS);
}




LONG queryTable(PGconn *conn)
{
 int                   numOutputCols; char                *col_name,sql[512],*cursorName =
"pgcursor",*msg,*sqlstate,*stmt= "pgstmt"; int                  num_rows = 10,column_index,dbtype; PGresult       *res; 
 // Start transaction res = PQexec(conn,"BEGIN");
 sprintf(sql," DECLARE %s CURSOR FOR SELECT  shortcol, intcol, int64col, realcol,  "   " doublecol, timecol,
blobcol,strcol FROM  %s", cursorName,TEST_TABLE); 
  res = PQprepare(conn, stmt,sql, 0 ,NULL);   if (PQresultStatus(res) != PGRES_COMMAND_OK &&       PQresultStatus(res)
!=PGRES_TUPLES_OK)   {       sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE);       msg =
PQresultErrorMessage(res);      if (msg != NULL && msg[0] != EOS  )         fprintf(stderr,"Error preparing select
statement:%s(%s)\n",msg,sqlstate? sqlstate:"");       return(-1); 
   }

   res = PQdescribePortal (conn,cursorName);   if (PQresultStatus(res) != PGRES_COMMAND_OK &&       PQresultStatus(res)
!=PGRES_TUPLES_OK)   {       msg = PQresultErrorMessage(res);       if (msg != NULL && msg[0] != EOS  )
fprintf(stderr,"Errordescribing select statement: %s\n",msg);       return(-1); 
   }
   numOutputCols  = PQnfields(res);
   // Get column information   for (column_index = 0; (column_index < numOutputCols) ; column_index++)   {     LONG
colmod=0;       /* Get the column name. */     col_name = PQfname(res,column_index);       dbtype =
PQftype(res,column_index);     if (col_name)         fprintf(stderr,"%s - %d \n ",col_name,dbtype);  } 
 res = PQexec(conn,"END");
 printf("Successfully queried and described %s.\n",TEST_TABLE); return(SE_SUCCESS);

}






> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, August 27, 2007 5:04 PM
> To: Brijesh Shrivastav
> Cc: pgsql-interfaces@postgresql.org
> Subject: Re: [INTERFACES] Issue with PQdescribePortal to describe a
> select cursor
>
>
> "Brijesh Shrivastav" <Bshrivastav@esri.com> writes:
> > Is there any known issue with using PQdescribePortal() against
> > a declared select server side cursor?
>
> Should work.  Can you provide a self-contained example of
> your problem?
>
>             regards, tom lane
>
>

"Brijesh Shrivastav" <Bshrivastav@esri.com> writes:
> Please see the test program below that creates a table and then
> queries it using DECLARE CURSOR statement.

Well, you only prepared the DECLARE CURSOR command, you didn't execute
it --- so there's no cursor, only a prepared statement sitting waiting
to be used.

Once you execute the DECLARE CURSOR, you should be able to describe its
portal in advance of fetching any rows from it.  Or is that not what
you wanted?

> ... (in many
> cases I need input bind parameter data from client application
> before executing the query).

I think you might be looking for Describe Statement (on a prepared
statement) rather than Describe Portal.  You have to have already
provided parameter values in order to have a portal at all...
        regards, tom lane

> Well, you only prepared the DECLARE CURSOR command, you didn't execute
> it --- so there's no cursor, only a prepared statement sitting waiting
> to be used.
>
> Once you execute the DECLARE CURSOR, you should be able to
> describe its
> portal in advance of fetching any rows from it.  Or is that not what
> you wanted?

I suspected the behavior you describe for portal but was hoping
that PQdescribePortal will work same way as PQdescribePrepared
work with prepared statement which is what we are using right now.
However, in our current scenario because of large dataset and many
concurrent users we are major performances issues and out of memory
errors in some cases. I am hoping the use of server side cursor will
help in better memory utilization for multiple users situation
and improve initial response (can you comment if this is a valid
assumption - I am sure about the initial response ).

My problem in executing a portal before I can describe is the way
our client api interface is defined. Typically user sends us a query
that we prepare and describe before sending the describe output
back to the client. In next step user provides us with any input
parameter data for the sql and ask us to execute the query. This
system works well with PQprepare/PQdescribePrepared but doesn't
give me the advantage of a portal. One option that will have a
performance penalty will be to prepare the statement without creating
a portal during initial query so I can send describe results back
to the client and only create the portal during the execute. I will
pay the cost of preparing query twice though I can set LIMIT to 0
for the initial query to possibly reduce the prepare time.

Thanks,
Brijesh

"Brijesh Shrivastav" <Bshrivastav@esri.com> writes:
> My problem in executing a portal before I can describe is the way
> our client api interface is defined. Typically user sends us a query
> that we prepare and describe before sending the describe output
> back to the client. In next step user provides us with any input
> parameter data for the sql and ask us to execute the query. This
> system works well with PQprepare/PQdescribePrepared but doesn't
> give me the advantage of a portal. One option that will have a
> performance penalty will be to prepare the statement without creating
> a portal during initial query so I can send describe results back
> to the client and only create the portal during the execute. I will
> pay the cost of preparing query twice though I can set LIMIT to 0
> for the initial query to possibly reduce the prepare time.

You seem to be confusing preparing a query with executing it.  LIMIT 0
isn't going to save any prepare time.
        regards, tom lane

>
> "Brijesh Shrivastav" <Bshrivastav@esri.com> writes:
> > My problem in executing a portal before I can describe is the way
> > our client api interface is defined. Typically user sends us a query
> > that we prepare and describe before sending the describe output
> > back to the client. In next step user provides us with any input
> > parameter data for the sql and ask us to execute the query. This
> > system works well with PQprepare/PQdescribePrepared but doesn't
> > give me the advantage of a portal. One option that will have a
> > performance penalty will be to prepare the statement
> without creating
> > a portal during initial query so I can send describe results back
> > to the client and only create the portal during the execute. I will
> > pay the cost of preparing query twice though I can set LIMIT to 0
> > for the initial query to possibly reduce the prepare time.
>
> You seem to be confusing preparing a query with executing it.  LIMIT 0
> isn't going to save any prepare time.

That is true, LIMIT 0 won't help me here. I was just trying to think of some
way where I can force a simple plan that takes less time to prepare since I am
going to disregard that plan in any case.

Brijesh