Timeout and exception with PostgreSQL (UniQuery and UniLoader)

[ael]

Hello,

I have 2 problems (they may be related) with UniDAC 6.2.8 on PostgreSQL 9.3. Basically the connection is closed - from the client side apparently - and an empty message or "Unexpected response from server" exception thrown.

The exception occurs:

- When running a TUniQuery for a SELECT * on a large table (13.8M records)

- When using a TUniLoader it crashes on TPgSQLLoader.Finish->PutCopyEnd (after storing ~1.2M records)

I tried to check and set the statement_timeout variable from within PostgreSQL and when connecting, but it seems to have no effect. I've also tried to set the "CommandTimeout" to 10 min but it didn't change anything.

Both cases work when the amount of data is small to medium (query of a few thousands record, using UniLoader to store 100k results) but fail on large table, apparently hitting an hidden timeout.

How can I debug this further? Is it a specific variable I failed to set?

[FCS]

Hello,

When you retrieve such a large number of records it is possible that the out of memory may occur, especially if the record length is large (a lot of fields) and you have 32 bit application.

Regards
Michal

[ael]

Hello,

I've dumped the problematic table to create a test-case and the resulting SQL file is 2.8GB so this will indeed be an issue in 32 bit.

However I don't think this is the problem here:
- The exception first occured in a 64 bit application
- The thrown exception is an EUnknown and EUnisomething, not an EOutOfMemory
- The exception occurs when the process (test case) consume ~470MB

I'll try to run the test case in 64 bit tomorrow and pinpoint if this is related to a specific number of rows or a timing. DBMonitor is unfortunately useless in this case.

Thanks for the insight.

[FCS]

Hello,

You can try to use FetchAll=false and set the number of received records for example 1000. Then iterate through them. The command "next" on the 1000-th record will receive next 1000 records.

Code: Select all

UQ.SpecificOptions.Values['FetchAll'] := 'false';
UQ.FetchRows:=1000;

Maybe this solve your problem, but I don't know the reason that you have to fetch all records from a table.

Regards
Michal

[ael]

Hello,

Unfortunately FetchAll=False is a huge performance hit and the same problem still occurs (only later).

but I don't know the reason that you have to fetch all records from a table

To create a dump of the database. This should be done by a DBA on the server side but we have to deal with client with very little database knowledge.

[ael]

It looks like an invisible hard limit somewhere in the PG provider (I can reproduce the crash with a memory footprint of 10MB). I've submitted a support request.

[azyk]

Please provide us with a sample and database dump, on which the problem is reproduced.

[ael]

Hello,

I've sent an email with the full test-case and database dump.

If it helps, I ran more tests and the exception is caused by enabling SSL on the database connection. In plain mode it works as expected (tested with OpenSSL 1.0.1 and 1.0.2d).

[azyk]

Thank you for the sent sample. We have reproduced this issue. We will investigate it and inform you as soon as we get any results.

[ael]

Any news about this issue?

[azyk]

We have fixed this problem. Please download the latest UniDAC version and reinstall it. Let us know if the error still persists.

[ael]

I'll check thank you. I hadn't seen anything about this in the changelog hence my question.

[azyk]

https://www.devart.com/unidac/revision_history.html

6.2.9 14-Dec-15
PostgreSQL data provider
•Bug with dumping character data with the Dump component is fixed