Re: Libpq driver: thread problem

I tested a bit the memory footprint.

Test bench:
psqlodbclibpq snapshot.
8.0.x PostgreSQL, and it's libpq.
Debian Sarge "isql" ODBC query tool.

I performed the following kind of query many times:
select * from test2 limit 2000000
It has about 5 varchar() columns.

The idea is, that redo exactly same query with isql
many times and watch the isql memory footprint.

I haven't checked the isql code, wether it closes
the statement before the next usage.

After the first select, the memory footprint was 368 meg.
If I iterated the query many times, the memory footprint
increased:

All memory consumptions are virtual memory usages:

4.5 meg (just connected into the database)
368 meg (after the first SELECT, no mem decrease detected)
502 meg (after the second SELECT,virt increased with 134 meg)
637 meg (virt increased with 135 meg)
772 meg (virt increased with 135 meg)

ps showed with one second update the following:
at the first SELECT, the memory usage increased
steadily during PostgreSQL was also active (inside libpq?).

Then it increased steadily before the stdout output began (
string format conversions for every row??)
During printing into stdout, the memory footprint kept the same
all the time.

After the first SELECT, the memory footprints were as follows:
Initially the memory footprint was 368 meg.
While transferring data from PostgreSQL into libpq, the memory
footprint did not increase.
Then it increased steadily into 502 meg, so this was during psqlodbc
internal processing. Again, when the stdout output began, the memory
footprint kept the same in 502 meg.

Conclusions:
- Somehow, psqlodbc does not free all allocated memory
resources after the SELECT has been closed.

- I don't know whether the isql does not call PQfree(), or
psqlodbc driver's charset conversion data output buffers are not freed.

- Memory footprints don't become doubled (500 meg -> 1000 meg with
realloc() ), so it seems, that the memory is allocated with small pieces.

- If I SELECT 4* 2 meg rows, memory footprint is 770 meg.
- If I SELECT 1*8 meg rows, memory footprint is 1400 meg. Twise!
Conclusion: half of the used memory is freed
with 4*2 meg row fetching.

UseDeclareFetch = 1
Fetch = 32
This failed with CONNECT with iodbc:

~$ isql <db> <uid> <pwd>
WARNING: there is already a transaction in progress
Muistialueen ylitys

So with libpq, the "BEGIN" seems to come twice with
UseDeclareFetch=1.

With UseDeclareFetch=1 almost every SELECT, even on CONNECT,
do use DECLARE <name> cursor for SELECT version() like queries.
So that's why CONNECT fails with that option.

CVS HEAD psqlodbc does the BEGIN correctly. psqlodbclibpq
seems to do the UseDeclareFetch BEGIN falsely, with and without
libpq.

I don't know, wether UseDeclareFetch behaviour is
the good way to go. One possibility would be a data filtering mode
(FORWARD_ONLY_MODE) with a very small memory footprint
on the client side.

Of course, if you need to support SELECT results with at least 8 million
rows, some (invisible) way to do it would be nice.

Implementing FORWARD_ONLY_MODE requires, that libpq would also
have a streamlined SELECT mode (without the CURSOR),
or then the CURSOR would be needed as it is done now.

Other thoughts, but no bugs:

I was able to SELECT almost 8 meg rows into memory with isql.
It took with Debian Sarge 1400 meg of virtual memory, but
isql did not crash. So the memory behaviour seems good, even though
I do like about small memory footprints.
(I must admit: I had to increase swap space for isql not to crash.)

Marko Ristola

Bruce Momjian wrote:

>Dave Page wrote:
>
>
>>>The main issue with the flag, as I remember, is to allow multiple
>>>threads to open libpq connections. If you don't do that, you
>>>don't need
>>>the flag.
>>>
>>>
>>In which case it definitely needs fixing. Which may be a non-trivial
>>task as pthreads on Windows is not currently used by PostgreSQL, and
>>didn't want to play last time I looked at it :-( However...
>>
>>I did look at this very briefly before speaking to Magnus. The first
>>problem I ran into was that configure was insisting that posix signals
>>were needed to enable thread safety. Before I spend lots of time looking
>>at the code do you know if it is safe for me to assume our signal
>>emaulation will do that job in all the right places? If so, I guess it's
>>just a case of fixing the pthread detection and linker flags.
>>
>>
>
>Ewe. I bet we added that test program _after_ we got threads working on
>Win32. That program, and the flags detection configure checks have made
>threads configuration almost fool-proof, so I don't think we should
>change any of that.
>
>As far as the Win32 API, I am unsure. Let me see if I can hack up
>thread_test.c to use libpq/pthread-win32.c to see if I can get that
>working.
>
>
>