pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue with PQdescribePortal to describe a select cursor
7+ messages / 2 participants
[nested] [flat]
* Issue with PQdescribePortal to describe a select cursor
@ 2007-08-27 17:54 Brijesh Shrivastav <Bshrivastav@esri.com>
0 siblings, 1 reply; 7+ messages in thread
From: Brijesh Shrivastav @ 2007-08-27 17:54 UTC (permalink / raw)
To: pgsql-interfaces
Hi! All,
I am trying to use server side cursor by declaring a select cursor
and then using FETCH statement to fetch few rows at a time for
faster initial response and hopefully better memory utilization.
Everything works as expected including faster initial response.
However, I run into issue when I try to describe the select cursor
using PQdescribePortal(). It complains about the non-existence of
portal (cursor). I ensured that I create the cursor name in lower case
but that doesn't seem to help.
Is there any known issue with using PQdescribePortal() against
a declared select server side cursor? I don't have any issue
using PQDescribePrepared for regular select statement. Will
I need to execute the declared cursor before I can call PQdescribePortal()?
Any help pointers will be useful.
Thanks,
Brijesh
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with PQdescribePortal to describe a select cursor
@ 2007-08-28 00:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brijesh Shrivastav <Bshrivastav@esri.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-08-28 00:04 UTC (permalink / raw)
To: Brijesh Shrivastav <Bshrivastav@esri.com>; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with PQdescribePortal to describe a select cursor
@ 2007-08-28 17:10 Brijesh Shrivastav <Bshrivastav@esri.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Brijesh Shrivastav @ 2007-08-28 17:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
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, "Connection to 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, "Query Table 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 DOUBLE PRECISION, 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,"Error describing 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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with PQdescribePortal to describe a select cursor
@ 2007-08-28 17:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brijesh Shrivastav <Bshrivastav@esri.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-08-28 17:29 UTC (permalink / raw)
To: Brijesh Shrivastav <Bshrivastav@esri.com>; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with PQdescribePortal to describe a select cursor
@ 2007-08-28 18:15 Brijesh Shrivastav <Bshrivastav@esri.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Brijesh Shrivastav @ 2007-08-28 18:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with PQdescribePortal to describe a select cursor
@ 2007-08-28 18:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brijesh Shrivastav <Bshrivastav@esri.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-08-28 18:24 UTC (permalink / raw)
To: Brijesh Shrivastav <Bshrivastav@esri.com>; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with PQdescribePortal to describe a select cursor
@ 2007-08-28 18:45 Brijesh Shrivastav <Bshrivastav@esri.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Brijesh Shrivastav @ 2007-08-28 18:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
>
> "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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-08-28 18:45 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-27 17:54 Issue with PQdescribePortal to describe a select cursor Brijesh Shrivastav <Bshrivastav@esri.com>
2007-08-28 00:04 ` Re: Issue with PQdescribePortal to describe a select cursor Tom Lane <tgl@sss.pgh.pa.us>
2007-08-28 17:10 ` Re: Issue with PQdescribePortal to describe a select cursor Brijesh Shrivastav <Bshrivastav@esri.com>
2007-08-28 17:29 ` Re: Issue with PQdescribePortal to describe a select cursor Tom Lane <tgl@sss.pgh.pa.us>
2007-08-28 18:15 ` Re: Issue with PQdescribePortal to describe a select cursor Brijesh Shrivastav <Bshrivastav@esri.com>
2007-08-28 18:24 ` Re: Issue with PQdescribePortal to describe a select cursor Tom Lane <tgl@sss.pgh.pa.us>
2007-08-28 18:45 ` Re: Issue with PQdescribePortal to describe a select cursor Brijesh Shrivastav <Bshrivastav@esri.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox