Smalltalk › Cincom › VisualWorks

PostgreSQL EXDI and large result sets

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

4 messages Options

Pavel Perikov

PostgreSQL EXDI and large result sets

I'm using VWNC 7.5. If i execute the query like 'select * from
table_with_ten_billions_of_records' i'm out of game. The semantics of
answer stream suggests cursor behavior but this is not the case.
PostgreSQL layer just keeps sucking records from socket till the
memory is over. Is this a known limitation? New versions, anyone? Any
plans to fix it? I think that simulating cursor behavior in user code
isn't a good idea.

Pavel

Mark Pirogovsky-3

Re: PostgreSQL EXDI and large result sets

Pavel,

With the databases you can either ask for the result set or you can open
cursor and fetch a records from it one by one, as many as you need.

And that is the semantic driven by your application design, not a
database layer, which gives you what you ask...

sess := connection getSession connect.
sess prepare: (sqlStmt := self query asString).
sess blockFactor: self blockFactor.
sess execute.
pro := [[ans := sess answer. ans == #noMoreAnswers] whileFalse:
[ans == #noAnswerStream ifFalse:
[[ans atEnd]whileFalse:
[| t |
list add: ans next]....]fork.

The above is a code from the AdHoqQuery tool which tries to populate
list. I just tried the Cartesian product query -- it did not break
anything, and I was able to terminate it easily ...

The problem usually -- populating the UI in the tight loop which kills
ones memory.

Pavel Perikov wrote:

> I'm using VWNC 7.5. If i execute the query like 'select * from
> table_with_ten_billions_of_records' i'm out of game. The semantics of
> answer stream suggests cursor behavior but this is not the case.
> PostgreSQL layer just keeps sucking records from socket till the memory
> is over. Is this a known limitation? New versions, anyone? Any plans to
> fix it? I think that simulating cursor behavior in user code isn't a
> good idea.
>
> Pavel
>
>
>

Alan Knight-2

Re: PostgreSQL EXDI and large result sets

In reply to this post by Pavel Perikov

This is a known limitation of the specific Postgresql driver. It's also on an old version of the Postgresql protocol. Using a different way of getting to Postgresql (e.g. via ODBC) should not exhibit this problem.

The Postgresql code is in an interesting state. It's contributed code, written originally by Bruce Badger. It's not officially supported by Cincom, however it is widely used, and in particular we use Postgresql for the public repository, and people are likely to use this code to access it. We'd also like it if someone did some work on that code, but don't have any internal expertise on it.

The easiest short-term workaround is that Postgresql supports limit and offset in queries, so you can simulate cursor behaviour that way at a coarse level.

At 08:58 AM 2/11/2008, Pavel Perikov wrote:

I'm using VWNC 7.5. If i execute the query like 'select * from
table_with_ten_billions_of_records' i'm out of game. The semantics of
answer stream suggests cursor behavior but this is not the case.
PostgreSQL layer just keeps sucking records from socket till the
memory is over. Is this a known limitation? New versions, anyone? Any
plans to fix it? I think that simulating cursor behavior in user code
isn't a good idea.

Pavel

Alan Knight [|], Cincom Smalltalk Development

[hidden email]

http://www.cincom.com/smalltalk

Pavel Perikov

Re: PostgreSQL EXDI and large result sets

Thanks Alan.

I will read about "offset". This way it doesn't look so ugly and will not take a lot of work. I thinks ODBC is not an option since I'm on Mac OSX?

Pavel.

On Feb 12, 2008, at 12:24 AM, Alan Knight wrote:

This is a known limitation of the specific Postgresql driver. It's also on an old version of the Postgresql protocol. Using a different way of getting to Postgresql (e.g. via ODBC) should not exhibit this problem.

The Postgresql code is in an interesting state. It's contributed code, written originally by Bruce Badger. It's not officially supported by Cincom, however it is widely used, and in particular we use Postgresql for the public repository, and people are likely to use this code to access it. We'd also like it if someone did some work on that code, but don't have any internal expertise on it.

The easiest short-term workaround is that Postgresql supports limit and offset in queries, so you can simulate cursor behaviour that way at a coarse level.

At 08:58 AM 2/11/2008, Pavel Perikov wrote:

I'm using VWNC 7.5. If i execute the query like 'select * from
table_with_ten_billions_of_records' i'm out of game. The semantics of
answer stream suggests cursor behavior but this is not the case.
PostgreSQL layer just keeps sucking records from socket till the
memory is over. Is this a known limitation? New versions, anyone? Any
plans to fix it? I think that simulating cursor behavior in user code
isn't a good idea.

Pavel

--

Alan Knight [|], Cincom Smalltalk Development

[hidden email]

[hidden email]

http://www.cincom.com/smalltalk