postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[postgresql-interfaces/psqlodbc] issue #96: Memory allocation error from PSQLODBC35W.DLL
3+ messages / 3 participants
[nested] [flat]
* [postgresql-interfaces/psqlodbc] issue #96: Memory allocation error from PSQLODBC35W.DLL
@ 2025-03-10 19:44 "ezekieloyeniran1 (@ezekieloyeniran1)" <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: ezekieloyeniran1 (@ezekieloyeniran1) @ 2025-03-10 19:44 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
I am currently experiencing an out-of-memory (OOM) error while using Copy Activity in a Pipeline to transfer 100M+ records from an On-Prem PostgreSQL database to a Microsoft Fabric Lakehouse.
The pipeline works for smaller subsets (~1M records) with parallelism set to 10 but consistently fails when attempting to load over 1 million records.
The PostgreSQL source is connected via ODBC (psqlodbc35w.dll), and an On Prem Data Gateway (version 3000.246.5) with 4 logical processors.
I would love an insight on knowing the limitation on the volume of data PSQLODBC35W.DLL driver can handle.
Do you think the constraints is from the On-Prem Data Gateway to transfer large data via Microsoft On-Prem Data Gateway?
Are there best practices for optimizing PostgreSQL reads to prevent failures?
Appreciate any insights!
Here is the error I got:
"```
_{
"errorCode": 2200,
"message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY001] Out of memory while reading tuples.;\nmemory allocation error???,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY001] Out of memory while reading tuples.;\nmemory allocation error???,Source=PSQLODBC35W.DLL,'",
"details": []
}_"
```
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #96: Memory allocation error from PSQLODBC35W.DLL
@ 2025-03-14 11:02 ` "davecramer (@davecramer)" <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: davecramer (@davecramer) @ 2025-03-14 11:02 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
I don't think this is an ODBC error per-se. The issue is that the way the protocol works is that if you do `select * from some_large_table` all of the records are read into memory before they are returned to you. So ultimately, you require enough memory to hold your result set
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #96: Memory allocation error from PSQLODBC35W.DLL
@ 2025-05-21 08:03 ` "markmaker (@markmaker)" <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: markmaker (@markmaker) @ 2025-05-21 08:03 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-05-21 08:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-10 19:44 [postgresql-interfaces/psqlodbc] issue #96: Memory allocation error from PSQLODBC35W.DLL "ezekieloyeniran1 (@ezekieloyeniran1)" <[email protected]>
2025-03-14 11:02 ` "davecramer (@davecramer)" <[email protected]>
2025-05-21 08:03 ` "markmaker (@markmaker)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox