TUniLoader problem with Postgres (unexpected message type 0x50)

[ael]

Hello,

To speed up large inserts (170K records) I use a TUniLoader.

Everything is working as expected with Firebird, but not with Postgres.

In binary mode the process just hangs while the server waits on a COPY BINARY command.

I tried to switch to text mode, but the following error is thrown out: "unexpected message type 0x50 during copy from stdin".

Is this something I can fix on the client side, does it require a specific configuration on the server? Is there a way to specify a "safer" way to load the data?

If it helps it's a PG 9.2.4 server.

[AlexP]

Hello,

We cannot reproduce the problem. Please send a small application demonstrating the problem, as well as the script for creating and filling in the DB objects.

[ael]

I found the root of the issue. However I'm not sure if it's intended or not and if it's a limitation of Postgres or UniDAC.

Basically, as I'm parsing a list of records with RTTI in the OnGetColumnData() I had to run a query which was marked as pending while the COPY BINARY ran. The processed was thus deadlocked and had to be manually stopped.

I resolved the deadlock by caching the required data before calling UniLoader.Load.

So as far as the issue goes everything works indeed as expected, however I'm curious if you can share some light on why this deadlock occurs?

[DemetrionQ]

Hello.

While executing the TUniLoader.Load command, you cannot execute SQL queries via the same connection. The point is that, when you run TUniLoader.Load, a COPY BINARY ... FROM STDIN command is sent to the server, then the server will wait for data retrieval in a particular format. If you attempt to execute any SQL query, you will either get the "unexpected message type" error or won't receive a respond from the server, because the server will wait for the load operation completion.
If you need to execute a SQL query while executing TUniLoader.Load, do it via another connection.

[ael]

Thank you for the clarification.

[DemetrionQ]

If any other questions come up, please contact us.