Thread: Fix for Declare/Fetch issue
Hi All, Please find attached the patch for Declare/Fetch issue. Sorry for the delay, but it took a while to get it right. Dave, please test it before I commit changes to CVS. My special thanks to Sivakumar and Vaidhy, for their contributions to this fix. Regards, Anoop > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc- > owner@postgresql.org] On Behalf Of Victor Rivero > Sent: Wednesday, October 26, 2005 4:55 AM > To: pgsql-odbc@postgresql.org > Subject: [ODBC] Declare/Fetch "Get Well Soon" > > > > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Dave Page > Sent: Jueves, 13 de Octubre de 2005 09:57 a.m. > Hello Dave, > > Any update on Declare/Fetch? News from Anoop? Date? > > Many thanks. Regards Victor Rivero > > > -----Original Message----- > > From: pgsql-odbc-owner@postgresql.org > > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Victor Rivero > > Sent: 13 October 2005 14:34 > > To: pgsql-odbc@postgresql.org > > Subject: Re: [ODBC] Problem with psqlODBC on "Cache Size" > > > > Thanks Dave. > > > > Any Expected Date available? > > > > We need this feature ASAP (not to rush anyone: stress on the > > "AP" part of > > it) for a live system that outgrew acceptable performance w/o > > Declare/Fetch > > (i.e. w/o cursors). > > > > I hope by the end of the month at the latest, however, I'll have to > leave it for Anoop to give a more useful answer as he is the one working > on the problem. > > Regards Dave > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
Attachment
Great, thanks Anoop. Can anyone test this fairly quickly? I don't have the appropriate hardware to hand for the next few days, and it would be helpful for a number of reasons if we could get this committed before November. Thanks, Dave. On 26/10/05 7:23 am, "Anoop Kumar" <anoopk@pervasive-postgres.com> wrote: > Hi All, > > Please find attached the patch for Declare/Fetch issue. Sorry for the > delay, but it took a while to get it right. > > Dave, please test it before I commit changes to CVS. > > My special thanks to Sivakumar and Vaidhy, for their contributions to > this fix. > > Regards, > Anoop > > >> -----Original Message----- >> From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc- >> owner@postgresql.org] On Behalf Of Victor Rivero >> Sent: Wednesday, October 26, 2005 4:55 AM >> To: pgsql-odbc@postgresql.org >> Subject: [ODBC] Declare/Fetch "Get Well Soon" >> >> >> >> -----Original Message----- >> From: pgsql-odbc-owner@postgresql.org >> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Dave Page >> Sent: Jueves, 13 de Octubre de 2005 09:57 a.m. >> Hello Dave, >> >> Any update on Declare/Fetch? News from Anoop? Date? >> >> Many thanks. Regards Victor Rivero >> >>> -----Original Message----- >>> From: pgsql-odbc-owner@postgresql.org >>> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Victor Rivero >>> Sent: 13 October 2005 14:34 >>> To: pgsql-odbc@postgresql.org >>> Subject: Re: [ODBC] Problem with psqlODBC on "Cache Size" >>> >>> Thanks Dave. >>> >>> Any Expected Date available? >>> >>> We need this feature ASAP (not to rush anyone: stress on the >>> "AP" part of >>> it) for a live system that outgrew acceptable performance w/o >>> Declare/Fetch >>> (i.e. w/o cursors). >>> >> >> I hope by the end of the month at the latest, however, I'll have to >> leave it for Anoop to give a more useful answer as he is the one > working >> on the problem. >> >> Regards Dave >> >> >> ---------------------------(end of > broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings Regards, Dave
Great. Thanks for Anoop and others. I hope that although I have a busy weekend, I would get a bit time for testing it under Linux. I don't have a testing environment under Windows. Regards, Marko Dave Page wrote: >Great, thanks Anoop. > >Can anyone test this fairly quickly? I don't have the appropriate hardware >to hand for the next few days, and it would be helpful for a number of >reasons if we could get this committed before November. > >Thanks, Dave. > > >On 26/10/05 7:23 am, "Anoop Kumar" <anoopk@pervasive-postgres.com> wrote: > > > >>Hi All, >> >>Please find attached the patch for Declare/Fetch issue. Sorry for the >>delay, but it took a while to get it right. >> >>Dave, please test it before I commit changes to CVS. >> >>My special thanks to Sivakumar and Vaidhy, for their contributions to >>this fix. >> >>Regards, >>Anoop >> >> >> >> >>>-----Original Message----- >>>From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc- >>>owner@postgresql.org] On Behalf Of Victor Rivero >>>Sent: Wednesday, October 26, 2005 4:55 AM >>>To: pgsql-odbc@postgresql.org >>>Subject: [ODBC] Declare/Fetch "Get Well Soon" >>> >>> >>> >>>-----Original Message----- >>>From: pgsql-odbc-owner@postgresql.org >>>[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Dave Page >>>Sent: Jueves, 13 de Octubre de 2005 09:57 a.m. >>>Hello Dave, >>> >>>Any update on Declare/Fetch? News from Anoop? Date? >>> >>>Many thanks. Regards Victor Rivero >>> >>> >>> >>>>-----Original Message----- >>>>From: pgsql-odbc-owner@postgresql.org >>>>[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Victor Rivero >>>>Sent: 13 October 2005 14:34 >>>>To: pgsql-odbc@postgresql.org >>>>Subject: Re: [ODBC] Problem with psqlODBC on "Cache Size" >>>> >>>>Thanks Dave. >>>> >>>>Any Expected Date available? >>>> >>>>We need this feature ASAP (not to rush anyone: stress on the >>>>"AP" part of >>>>it) for a live system that outgrew acceptable performance w/o >>>>Declare/Fetch >>>>(i.e. w/o cursors). >>>> >>>> >>>> >>>I hope by the end of the month at the latest, however, I'll have to >>>leave it for Anoop to give a more useful answer as he is the one >>> >>> >>working >> >> >>>on the problem. >>> >>>Regards Dave >>> >>> >>>---------------------------(end of >>> >>> >>broadcast)--------------------------- >> >> >>>TIP 5: don't forget to increase your free space map settings >>> >>> > >Regards, Dave > > > >---------------------------(end of broadcast)--------------------------- >TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
My test case failed.
I tested only column wise query.
It returned the correct number of rows, but only the first cursor fetch got
correct data.
The self contained test case is attached:
it creates a test table, inserts there six rows and fetches
those rows as column wise. Please use Fetch=2 with it.
Finally it drops the test table.
You need to change DATABASE, USERID and PASSWORD.
It worked without Declare/fetch as it should:
OK(UseDeclareFetch=0):
After colwise query: data[1] = { 4, 0001 }, data[2] = { 4, 1001 }
After colwise query: data[1] = { 4, 2001 }, data[2] = { 4, 3001 }
After colwise query: data[1] = { 4, 4001 }, data[2] = { 4, 5001 }
ok.
(4= number of characters. "0001" is a character string value.)
FAILED (UseDeclareFetch=1,Fetch=2):
After colwise query: data[1] = { 4, 0001 }, data[2] = { 4, 1001 }
After colwise query: data[1] = { -1, UNSET }, data[2] = { -1, UNSET }
After colwise query: data[1] = { -1, UNSET }, data[2] = { -1, UNSET }
ok.
-1 means possibly a NULL value(?).
UNSET means that the string buffer is uninitialized after SQLFetchScroll.
Unfreed memory was a problem with this failed test case.
You can confirm that by setting the number of rows into 600 000 and
using Fetch=2 with Declare/Fetch.
The program prints
"INITIALIZATION OF 600000 ROWS DONE". Then the process's used memory
grows very fast.
Regards,
Marko Ristola.
Dave Page wrote:
>Great, thanks Anoop.
>
>Can anyone test this fairly quickly? I don't have the appropriate hardware
>to hand for the next few days, and it would be helpful for a number of
>reasons if we could get this committed before November.
>
>Thanks, Dave.
>
>
>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#define NUMBEROF_TEST_ROWS (6)
int CHECK(SQLSMALLINT handle_type,SQLHANDLE handle,SQLRETURN ret)
{
if (ret != SQL_SUCCESS && ret != SQL_NO_DATA)
{
SQLRETURN ret;
SQLINTEGER i = 0;
SQLCHAR state[32];
SQLINTEGER native;
SQLCHAR text[512];
SQLSMALLINT text_len;
int has_err = 0;
do {
i++;
ret = SQLGetDiagRec(handle_type,handle,i,state,&native,text,sizeof(text), &text_len);
fprintf(stderr,"message=%s\nstate=%s native error %d.\n",text,state,native);
has_err=1;
} while(ret == SQL_SUCCESS);
if (!has_err)
fprintf(stderr,"An unknonw error occurred\n");
}
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO || ret == SQL_NO_DATA)
return 1;
return 0;
}
// PREPARATIONS
int CreateInitialData(SQLHANDLE m_env,SQLHANDLE m_conn)
{
int row;
int nextVacuum=1024;
SQLHANDLE stmt;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLSetConnectOption(m_conn,SQL_AUTOCOMMIT,0)))
return 1;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLAllocHandle(SQL_HANDLE_STMT,m_conn,&stmt)))
return 1;
if (!CHECK(SQL_HANDLE_STMT,stmt,SQLExecDirect(stmt,(SQLCHAR*)"CREATE TABLE test_testcolwisequery ("
" key1 VARCHAR(250),"
" key2 VARCHAR(250),"
" value1 VARCHAR(250),"
" value2 VARCHAR(250),"
" primary key(key1,key2))",SQL_NTS)))
goto fail;
for (row=0; row < NUMBEROF_TEST_ROWS; row++)
{
char key1[200],key2[200],value1[200];
char sqlcmd[512];
sprintf(key1,"%d000",row);
sprintf(key2,"%d001",row);
sprintf(value1,"%d002",row);
sprintf(sqlcmd,"INSERT INTO test_testcolwisequery(key1,key2,value1) values( '%s' , '%s' , '%s' )",
key1,key2,value1);
if (!CHECK(SQL_HANDLE_STMT,stmt,SQLExecDirect(stmt,(SQLCHAR*)sqlcmd,SQL_NTS)))
goto fail;
if (row % 10000 == 0) {
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
goto fail;
}
if (row == nextVacuum) {
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
goto fail;
if (!CHECK(SQL_HANDLE_STMT,stmt,SQLExecDirect(stmt,(SQLCHAR*)"VACUUM ANALYZE test_testcolwisequery",SQL_NTS)))
goto fail;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
goto fail;
nextVacuum *=2;
}
}
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
goto fail;
SQLFreeHandle(SQL_HANDLE_STMT,stmt);
if (NUMBEROF_TEST_ROWS > 100)
printf("INITIALIZATION OF %d ROWS DONE.\n",NUMBEROF_TEST_ROWS);
return 1;
fail:
SQLFreeHandle(SQL_HANDLE_STMT,stmt);
return 0;
}
int DropTestEnv(SQLHANDLE m_env,SQLHANDLE m_conn)
{
SQLHANDLE stmt;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLAllocHandle(SQL_HANDLE_STMT,m_conn,&stmt)))
return 1;
if (!CHECK(SQL_HANDLE_STMT,stmt,SQLExecDirect(stmt,(SQLCHAR*)"DROP TABLE test_testcolwisequery",SQL_NTS)))
goto fail;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
goto fail;
SQLFreeHandle(SQL_HANDLE_STMT,stmt);
return 1;
fail:
SQLFreeHandle(SQL_HANDLE_STMT,stmt);
return 0;
}
// PREPARATIONS END
#define TEXT_SIZE 300
SQLINTEGER data_len[2];
char data[2][TEXT_SIZE];
void initBufs() {
data_len[0]=-123;
data_len[1]=-123;
strcpy(data[0],"UNSET");
strcpy(data[1],"UNSET");
}
void PrintData(char *msg,SQLUINTEGER nrows)
{
unsigned int i;
printf("%s: ",msg);
for (i=0; i<nrows; i++) {
if (i) printf(", ");
printf("data[%d] = { %d, %s }",i+1,(int)data_len[i],data[i]);
}
printf("\n");
fflush(stdout);
}
/* You need the following definition:
* CREATE TABLE test_testcolwisequery(value1 VARCHAR(100));
*
*
*/
#define USERID "marko"
#define DATABASE "marko"
#define PASSWORD "marko"
int main(int argc, char **argv)
{
SQLHANDLE m_env;
SQLHANDLE m_conn;
SQLHANDLE m_stmt;
SQLUINTEGER fetchedRows;
SQLINTEGER statuses[2];
SQLRETURN rc;
if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_env))
{
fprintf(stderr,"Allocating ENV handle failed.\n");
return 1;
}
if (!CHECK(SQL_HANDLE_ENV,m_env,SQLSetEnvAttr(m_env, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3,0)))
return 1;
if (!CHECK(SQL_HANDLE_ENV,m_env,SQLAllocHandle(SQL_HANDLE_DBC,m_env,&m_conn)))
return 1;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLConnect(m_conn,(SQLCHAR*) DATABASE,
SQL_NTS,(SQLCHAR*) USERID,SQL_NTS,(SQLCHAR*) PASSWORD,SQL_NTS)))
return 1;
if (!CreateInitialData(m_env,m_conn))
return 1;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLAllocHandle(SQL_HANDLE_STMT,m_conn,&m_stmt)))
return 1;
if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLSetStmtAttr(m_stmt, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN,0)))
return 1;
if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLSetStmtAttr(m_stmt, SQL_ATTR_ROW_ARRAY_SIZE, (void*)2,0)))
return 1;
if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLSetStmtAttr(m_stmt, SQL_ATTR_ROW_STATUS_PTR, statuses,0)))
return 1;
if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLSetStmtAttr(m_stmt, SQL_ATTR_ROWS_FETCHED_PTR, &fetchedRows,0)))
return 1;
if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLBindCol(m_stmt,1, // Column number
SQL_C_CHAR, // C data type
&data[0], // parameter value ptr
TEXT_SIZE, // buffer length
(SQLINTEGER*)&data_len[0]))) // string length ptr
return 1;
if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLExecDirect(m_stmt,(SQLCHAR*) "SELECT key2 FROM test_testcolwisequery",
SQL_NTS)))
return 1;
initBufs();
while ( (rc = SQLFetchScroll(m_stmt,SQL_FETCH_NEXT, 0)) != SQL_NO_DATA) {
PrintData("After colwise query",fetchedRows);
initBufs();
}
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLFreeStmt(m_stmt,SQL_CLOSE)))
return 1;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
return 1;
// if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_ROLLBACK)))
// return 1;
if (!DropTestEnv(m_env,m_conn))
return 1;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
return 1;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLFreeStmt(m_stmt,SQL_DROP)))
return 1;
if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLDisconnect(m_conn)))
return 1;
if (!CHECK(SQL_HANDLE_ENV,m_env,SQLFreeConnect(m_conn)))
return 1;
if (SQL_SUCCESS != SQLFreeEnv(m_env))
{
fprintf(stderr,"Connect env freeing failed\n");
return 1;
}
fprintf(stderr,"ok.\n");
return 0;
}
> -----Original Message----- > From: Anoop Kumar [mailto:anoopk@pervasive-postgres.com] > Sent: 26 October 2005 07:24 > To: pgsql-odbc@postgresql.org; Dave Page > Cc: Victor Rivero > Subject: Fix for Declare/Fetch issue > > Hi All, > > Please find attached the patch for Declare/Fetch issue. Sorry for the > delay, but it took a while to get it right. > > Dave, please test it before I commit changes to CVS. OK, it appears OK in my simple tests, though I note that Marko has spotted an issue. I've committed the fix for now, as it is definitely better than it was. Thanks guys. Regards, Dave.
> -----Original Message----- > From: Marko Ristola [mailto:Marko.Ristola@kolumbus.fi] > Sent: 30 October 2005 14:07 > To: Dave Page > Cc: Anoop Kumar; pgsql-odbc@postgresql.org; Victor Rivero > Subject: Re: [ODBC] Fix for Declare/Fetch issue > > > My test case failed. > > I tested only column wise query. > It returned the correct number of rows, but only the first > cursor fetch got > correct data. Hmm, OK. I don't have time for at least a couple of days to look further - Anoop, can you confirm/fix the bug? Just for info, we are hoping to release 8.1 early next week, so I need to ideally get things sorted by the end of this week. Regards, Dave.
Hi Dave, hi Anoop, In qresult.c you still find: 515 if (fetch_count < fetch_count) Declare/Fetch will not work without changing that, i.e. just fetch one line. 515 if (fetch_count < num_backend_rows) (Dave's proposal) seems to be a better choice. regards, Johann