Message-ID: <gh-postgresql-interfaces-psqlodbc-96-c2896991762@github.com>
From: "markmaker (@markmaker)" <noreply+markmaker@github.com>
To: "postgresql-interfaces/psqlodbc" <noreply+postgresql-interfaces-psqlodbc@github.com>
Date: Wed, 21 May 2025 08:03:31 +0000
Subject: Re: [postgresql-interfaces/psqlodbc] issue #96: Memory allocation error from PSQLODBC35W.DLL
In-Reply-To: <gh-postgresql-interfaces-psqlodbc-96@github.com>
References: <gh-postgresql-interfaces-psqlodbc-96@github.com>
List-Id: <gh-postgresql-interfaces-psqlodbc.github.com>
X-GitHub-Author-Login: markmaker
X-GitHub-Comment-Id: 2896991762
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 96
X-GitHub-Repo: postgresql-interfaces/psqlodbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/96#issuecomment-2896991762
Content-Type: text/plain; charset=utf-8

I had the same problem, and this is how I had to solve it:

1. Use the original query `where` expression etc. but only fetch the primary keys of the result set records. Assuming the primary key is some rather small ID data type, it will allow for very large result sets, even in constrained RAM.
2. Then select the actual records in batches of a comfortable size, i.e. `select ... where yourPrimaryKey in (?, ?, ?, ?, ...)`

This seems to work well, PostgreSQL does the batch fetching fast (as it happens, the overall performance is still faster than the original DB we used before). We even just prepare one batch fetching statement, and fill any surplus bound params with `null` for the last remainder fetch, and it doesn't seem to bother.

During the query processing, if you are updating or even deleting records further down the data set, be aware this will now be reflected in the batch-fetched records, unlike with the original full-fetch query. We actually wanted that, it solved a secondary problem for us.

_Mark