PostgeSQL ODBC - Declare/Fetch

Author

Declare/Fetch

Johann Zuschlag

2005-11-02, 7:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Dave Page

2005-11-02, 7:23 am

> -----Original Message-----
> From: Johann Zuschlag & #91;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

Marko Ristola

2005-11-02, 11:23 am

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:

>
>
>
>
>
>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 :-(
>
>
>
>
>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
>
>
>

Dave Page

2005-11-02, 11:23 am

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:
>
> to the bug
> help would be
> broadcast)---------------------------
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Marko Ristola

2005-11-03, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Dave Page

2005-11-03, 8:24 pm

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

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

Sponsored Links