Thread: Re: Declare/Fetch

Re: Declare/Fetch

From
"Dave Page"
Date:

> -----Original Message-----
> From: Johann Zuschlag [mailto:zuschlag2@online.de]
> Sent: 02 November 2005 13:02
> To: Dave Page; anoopk@pervasive-postgres.com;
> pgsql-odbc@postgresql.org
> Subject: Declare/Fetch
>
> Hi Dave, hi Anoop,

Hi Johann,

Unfortunately Anoop et al. are out for a few days, so I'm desperately
trying to understand and fix this before 8.1 :-(

> 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.

Updated in my local copy (thanks) - unfortunately not a fix to the bug
reported by Marko.

I have narrowed it down some more though - it's not so much a colwise
issue, as a bind issue. You can see it with the following:

1) Set cache size to 2 and enable Declare/Fetch
2) Connect
3) SQLExecDirect "SELECT relname FROM pg_class"
4) Bind to column 1
5) SQLFetch
6) SQLFetch
7) SQLFetch *bang* :-)

I'm largely unfamiliar with this part of the code so any help would be
appreciated. FWIW, the bug seems to be libpq version specific - it's
certainly not in 07.xx.

Regards, dave

Re: Declare/Fetch

From
Marko Ristola
Date:
I tested this a bit more now.
I tried to make more small test cases.

Here are the results:

Normal query okay without SQLBindCol (use SQLGetData for getting the data):

marko@amilo:~/workspace/SQLLIB/tests$ ./TestQuery
data = { 4, 0001 }
data = { 4, 1001 }
data = { 4, 2001 }
data = { 4, 3001 }
data = { 4, 4001 }
data = { 4, 5001 }

ok.


SQLBindCol used, but not fetching many rows:

marko@amilo:~/workspace/SQLLIB/tests$ ./TestPreparedQuery
 data[1] = { 4, 0001 }
 data[1] = { 4, 1001 }
 data[1] = { -1, UNSET }
 data[1] = { -1, UNSET }
 data[1] = { -1, UNSET }
 data[1] = { -1, UNSET }

SQLBindCol used, with columnwise query:

marko@amilo:~/workspace/SQLLIB/tests$ ./TestColwiseQuery
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.

So the problem seems to be always, if SQLBindCol is used.
In that case the new libpq resultset is lost ???.

So SQLBindCol() loses the data always after the first fetch.


TestPreparedQuery.c contains this simplest test case.
It is not prepared although, but it uses SQLBindCol().

Marko

Dave Page wrote:

>
>
>
>
>>-----Original Message-----
>>From: Johann Zuschlag [mailto:zuschlag2@online.de]
>>Sent: 02 November 2005 13:02
>>To: Dave Page; anoopk@pervasive-postgres.com;
>>pgsql-odbc@postgresql.org
>>Subject: Declare/Fetch
>>
>>Hi Dave, hi Anoop,
>>
>>
>
>Hi Johann,
>
>Unfortunately Anoop et al. are out for a few days, so I'm desperately
>trying to understand and fix this before 8.1 :-(
>
>
>
>>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.
>>
>>
>
>Updated in my local copy (thanks) - unfortunately not a fix to the bug
>reported by Marko.
>
>I have narrowed it down some more though - it's not so much a colwise
>issue, as a bind issue. You can see it with the following:
>
>1) Set cache size to 2 and enable Declare/Fetch
>2) Connect
>3) SQLExecDirect "SELECT relname FROM pg_class"
>4) Bind to column 1
>5) SQLFetch
>6) SQLFetch
>7) SQLFetch *bang* :-)
>
>I'm largely unfamiliar with this part of the code so any help would be
>appreciated. FWIW, the bug seems to be libpq version specific - it's
>certainly not in 07.xx.
>
>Regards, dave
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>
>


#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_testpreparedquery ("
      "  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_testpreparedquery(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_testpreparedquery",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_testpreparedquery",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[1];
char data[1][TEXT_SIZE];

void initBufs() {
    data_len[0]=-123;

    strcpy(data[0],"UNSET");
}

static 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_testpreparedquery(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;
  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,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_testpreparedquery",
SQL_NTS)))
    return 1;

  initBufs();
  while ( (rc = SQLFetch(m_stmt)) != SQL_NO_DATA) {

    if (!CHECK(SQL_HANDLE_STMT,m_stmt,rc))
      return 1;

    PrintData("After colwise query",1);
    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;
}


Re: Declare/Fetch

From
"Dave Page"
Date:
Hi Marko,

I just committed a fix for this which passes your test program, and a
variety of manual tests in the MS ODBC test program.

Basically what was happening was that each set of results was read into
the same block of cache, but when it extracted the values to send to
copy_and_convert, it assume that each tuple was offset by the total
number of tuples from the start of the cache, where it was actually
offset by the tuple number within that set. If that makes sense :-).

Can you give it a whirl please?

Regards, Dave.

> -----Original Message-----
> From: Marko Ristola [mailto:Marko.Ristola@kolumbus.fi]
> Sent: 02 November 2005 16:16
> To: Dave Page
> Cc: Johann Zuschlag; anoopk@pervasive-postgres.com;
> pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Declare/Fetch
>
>
> I tested this a bit more now.
> I tried to make more small test cases.
>
> Here are the results:
>
> Normal query okay without SQLBindCol (use SQLGetData for
> getting the data):
>
> marko@amilo:~/workspace/SQLLIB/tests$ ./TestQuery
> data = { 4, 0001 }
> data = { 4, 1001 }
> data = { 4, 2001 }
> data = { 4, 3001 }
> data = { 4, 4001 }
> data = { 4, 5001 }
>
> ok.
>
>
> SQLBindCol used, but not fetching many rows:
>
> marko@amilo:~/workspace/SQLLIB/tests$ ./TestPreparedQuery
>  data[1] = { 4, 0001 }
>  data[1] = { 4, 1001 }
>  data[1] = { -1, UNSET }
>  data[1] = { -1, UNSET }
>  data[1] = { -1, UNSET }
>  data[1] = { -1, UNSET }
>
> SQLBindCol used, with columnwise query:
>
> marko@amilo:~/workspace/SQLLIB/tests$ ./TestColwiseQuery
> 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.
>
> So the problem seems to be always, if SQLBindCol is used.
> In that case the new libpq resultset is lost ???.
>
> So SQLBindCol() loses the data always after the first fetch.
>
>
> TestPreparedQuery.c contains this simplest test case.
> It is not prepared although, but it uses SQLBindCol().
>
> Marko
>
> Dave Page wrote:
>
> >
> >
> >
> >
> >>-----Original Message-----
> >>From: Johann Zuschlag [mailto:zuschlag2@online.de]
> >>Sent: 02 November 2005 13:02
> >>To: Dave Page; anoopk@pervasive-postgres.com;
> >>pgsql-odbc@postgresql.org
> >>Subject: Declare/Fetch
> >>
> >>Hi Dave, hi Anoop,
> >>
> >>
> >
> >Hi Johann,
> >
> >Unfortunately Anoop et al. are out for a few days, so I'm desperately
> >trying to understand and fix this before 8.1 :-(
> >
> >
> >
> >>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.
> >>
> >>
> >
> >Updated in my local copy (thanks) - unfortunately not a fix
> to the bug
> >reported by Marko.
> >
> >I have narrowed it down some more though - it's not so much a colwise
> >issue, as a bind issue. You can see it with the following:
> >
> >1) Set cache size to 2 and enable Declare/Fetch
> >2) Connect
> >3) SQLExecDirect "SELECT relname FROM pg_class"
> >4) Bind to column 1
> >5) SQLFetch
> >6) SQLFetch
> >7) SQLFetch *bang* :-)
> >
> >I'm largely unfamiliar with this part of the code so any
> help would be
> >appreciated. FWIW, the bug seems to be libpq version specific - it's
> >certainly not in 07.xx.
> >
> >Regards, dave
> >
> >---------------------------(end of
> broadcast)---------------------------
> >TIP 3: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faq
> >
> >
> >
>
>

Re: Declare/Fetch

From
Marko Ristola
Date:
Hi all.

I couldn't confirm, that the memory behaviour is good
for Declare/Fetch.

My tests lost memory about 125M, but
the behaviour should be good with forward only
cursors (about 5M proccess memory).

Without Declare/Fetch query result consumed 40M memory (600000 rows fetch).

I used Fetch=2, because it tells the worst behaviour.

So the idea with Declare/Fetch is that the memory footprint with the
process is constant,
while reading millions of rows from the database.

Regards,
Marko Ristola

Dave Page wrote:

>Hi Marko,
>
>I just committed a fix for this which passes your test program, and a
>variety of manual tests in the MS ODBC test program.
>
>Basically what was happening was that each set of results was read into
>the same block of cache, but when it extracted the values to send to
>copy_and_convert, it assume that each tuple was offset by the total
>number of tuples from the start of the cache, where it was actually
>offset by the tuple number within that set. If that makes sense :-).
>
>Can you give it a whirl please?
>
>Regards, Dave.
>
>



Re: Declare/Fetch

From
"Dave Page"
Date:

> -----Original Message-----
> From: Marko Ristola [mailto:Marko.Ristola@kolumbus.fi]
> Sent: 02 November 2005 22:16
> To: Dave Page
> Cc: Johann Zuschlag; anoopk@pervasive-postgres.com;
> pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Declare/Fetch
>
>
> Hi all.
>
> I couldn't confirm, that the memory behaviour is good
> for Declare/Fetch.
>
> My tests lost memory about 125M, but
> the behaviour should be good with forward only
> cursors (about 5M proccess memory).

Aww, nuts. I knew there was something else :-)

I'm pretty sure I know what's wrong - I'll get onto it shortly.

Just for info, once we're happy this is fixed, I'll be rolling an
official release so we have something to bundle with pgInstaller 8.1,
and to (hopefully) get into FC5.

Regards, Dave.