NO DATA error message in Frontend when querying large datasets

hello,

we have postgres 9.0.1 installed on a windows 2008 r2 server.
odbc is psqlodbc_09_00_0200.zip.
the database has currently a size of around 800mb.

it works as expected on the server (pgadmin), but if we try queries for
large datasets we get the error message: "No data" from our frontend or
any other database tools thats using the ODBC connection (e.g. anysql
maestro).

1.) first we will make a query with only 1 column. we get data.
2.) then make the same query but with all columns: it says "No data".
3.) Afterwards we try the query from before again, but still getting "No
data".

Only a reconnect will "fix" that!
here are the results:

select sample_number from result
Query OK, 325194 rows affected (28.75 sec)

select * from result
Query OK, 0 rows affected (8.75 sec)

select sample_number from result
Query OK, 0 rows affected (0.20 sec)

cheers jk

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: NO DATA error message in Frontend when querying large datasets

Jens Kapp writes:
> we have postgres 9.0.1 installed on a windows 2008 r2 server.
> odbc is psqlodbc_09_00_0200.zip.
> the database has currently a size of around 800mb.

> it works as expected on the server (pgadmin), but if we try queries for
> large datasets we get the error message: "No data" from our frontend or
> any other database tools thats using the ODBC connection (e.g. anysql
> maestro).

> 1.) first we will make a query with only 1 column. we get data.
> 2.) then make the same query but with all columns: it says "No data".
> 3.) Afterwards we try the query from before again, but still getting "No
> data".

I wonder whether you're running out of memory for the query result in
the client side, and this "no data" is coming from some code that is not
written to cope with that happening.

You might consider using a cursor to fetch large results a few rows at a
time.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: NO DATA error message in Frontend when querying largedatasets

hi tom,

thanks a lot for your response.
the frontend we are trying to use is a well known and spread laboratory
system. our consultant has experience with other databases (oracle, sql)
that are a lot bigger - with no problems. however i'm wondering that
postgres is not working with the lab system AND other common DB
frontends using ODBC. i guess there is no setting for: "use cursor
instead" ?

thanks
jk


Am 26.01.2011 20:16, schrieb Tom Lane:
> Jens Kapp writes:
>> we have postgres 9.0.1 installed on a windows 2008 r2 server.
>> odbc is psqlodbc_09_00_0200.zip.
>> the database has currently a size of around 800mb.
>> it works as expected on the server (pgadmin), but if we try queries for
>> large datasets we get the error message: "No data" from our frontend or
>> any other database tools thats using the ODBC connection (e.g. anysql
>> maestro).
>> 1.) first we will make a query with only 1 column. we get data.
>> 2.) then make the same query but with all columns: it says "No data".
>> 3.) Afterwards we try the query from before again, but still getting "No
>> data".
> I wonder whether you're running out of memory for the query result in
> the client side, and this "no data" is coming from some code that is not
> written to cope with that happening.
>
> You might consider using a cursor to fetch large results a few rows at a
> time.
>
> regards, tom lane
>

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: NO DATA error message in Frontend when querying large datasets

> thanks a lot for your response.
> the frontend we are trying to use is a well known and spread laboratory s=
ystem. our consultant has experience with other databases (oracle, sql) tha=
t are a lot bigger - with no problems. however i'm wondering that postgres =
is not working with the lab system AND other common DB frontends using ODBC=
.. i guess there is no setting for: "use cursor instead" ?

"Use Declare/Fetch" may be the setting that you want. That's a setting in =
the ODBC driver and you should be able to change that setting through the W=
indows ODBC Administrator.

You may also want to change the "Server side prepare" setting. (Be sure to=
change one setting at a time and test your application between changes).

-- Korry

>=20
> Am 26.01.2011 20:16, schrieb Tom Lane:
>> Jens Kapp writes:
>>> we have postgres 9.0.1 installed on a windows 2008 r2 server.
>>> odbc is psqlodbc_09_00_0200.zip.
>>> the database has currently a size of around 800mb.
>>> it works as expected on the server (pgadmin), but if we try queries for
>>> large datasets we get the error message: "No data" from our frontend or
>>> any other database tools thats using the ODBC connection (e.g. anysql
>>> maestro).
>>> 1.) first we will make a query with only 1 column. we get data.
>>> 2.) then make the same query but with all columns: it says "No data".
>>> 3.) Afterwards we try the query from before again, but still getting "No
>>> data".
>> I wonder whether you're running out of memory for the query result in
>> the client side, and this "no data" is coming from some code that is not
>> written to cope with that happening.
>>=20
>> You might consider using a cursor to fetch large results a few rows at a
>> time.
>>=20
>> regards, tom lane
>>=20
>=20
> --=20
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

------------------------------------------------------------ -----------
Korry Douglas
Senior Database Dude
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: (804)241-4301
Mobile: (620) EDB-NERD



--=20
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: NO DATA error message in Frontend when querying largedatasets

thanks a lot, "Declare/Fetch" fixed our issue!


Am 27.01.2011 00:36, schrieb Korry Douglas:
>> thanks a lot for your response.
>> the frontend we are trying to use is a well known and spread laboratory system. our consultant has experience with other databases (oracle, sql) that are a lot bigger - with no problems. however i'm wondering that postgres is not working with the lab system AND other common DB frontends using ODBC. i guess there is no setting for: "use cursor instead" ?
> "Use Declare/Fetch" may be the setting that you want. That's a setting in the ODBC driver and you should be able to change that setting through the Windows ODBC Administrator.
>
> You may also want to change the "Server side prepare" setting. (Be sure to change one setting at a time and test your application between changes).
>
> -- Korry
>
>> Am 26.01.2011 20:16, schrieb Tom Lane:
>>> Jens Kapp writes:
>>>> we have postgres 9.0.1 installed on a windows 2008 r2 server.
>>>> odbc is psqlodbc_09_00_0200.zip.
>>>> the database has currently a size of around 800mb.
>>>> it works as expected on the server (pgadmin), but if we try queries for
>>>> large datasets we get the error message: "No data" from our frontend or
>>>> any other database tools thats using the ODBC connection (e.g. anysql
>>>> maestro).
>>>> 1.) first we will make a query with only 1 column. we get data.
>>>> 2.) then make the same query but with all columns: it says "No data".
>>>> 3.) Afterwards we try the query from before again, but still getting "No
>>>> data".
>>> I wonder whether you're running out of memory for the query result in
>>> the client side, and this "no data" is coming from some code that is not
>>> written to cope with that happening.
>>>
>>> You might consider using a cursor to fetch large results a few rows at a
>>> time.
>>>
>>> regards, tom lane
>>>
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
> ------------------------------------------------------------ -----------
> Korry Douglas
> Senior Database Dude
> EnterpriseDB Corporation
> The Enterprise Postgres Company
>
> Phone: (804)241-4301
> Mobile: (620) EDB-NERD
>
>
>

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs