Devart Forums

Hello, today I downloaded Devart ODBC driver for PostgreSQL (2.02.06.00), configured a DNS to PostgreSQL 9.5 (localhost, default port 5432, some name/password, nothing else changed)
I'm getting errors
(HY000 )[Devart][ODBC][PostgreSQL]cannot execute INSERT in a read-only transaction ErrorCode= 2278816

when I try to do UPSERT.
If I use PostgreSQL ODBC (9.05.04) or EnterpriseDB (9.05.300.01), all works ok.

I was in fact trying to test Devart ODBC to see whether there's any performance improvement when doing SQLExecute on multiple rows (when comparing performance of UPSERT-ing several rows by every SQLExecute to a single-row there was no visible improvement using PostgreSQL ODBC; looking at ODBC logs it seems that internally SQLExecute makes a call to database for every row - exactly as this post reports: https://www.postgresql.org/message-id/5 ... na.SAS.com )

Please specify the tool/application you are using when working with our driver and provide us with the steps to reproduce the specified error.

Describe in more detail the commands/action list you are testing for performance of data insertion:
- BULK commands
- composing INSERT/INTO SQL query and a parameters array
- others.

If you have a small test project for the above actions, send us to investigate (using the contact form http://www.devart.com/company/contactform.html ). Also include the scripts to create test tables in the sample.

Hello Azyk,
I apologize for a false alarm. The target database was indeed read only :(
(SET default_transaction_read_only = 'true'). And I had a slightly different configuration of Devart ODBC than Postgres/Enterprise. So the basic error was between chair and a keyboard..

Just to let people know: when I tried my application with Devart ODBC and multi-row inserts, I didn't see any significant speed improvements. Each SQLExecute inserts 1000 rows and a commit is performed after 100 SQLExecutes (that is 100k rows). My code seems to run with the same speed, no matter whether PgSql, EnterpriseDB or Devart ODBC is used.

Currently, we are investigating BATCH operations. As a result of this investigation, we hope to get a significant increase in performance when inserting/updating multiple rows data. We will let you know the results.

That's an excellent news.
In the meantime, I made a workaround - first I tried to use COPY TABLE, then I found a forum topic suggesting using file_fdw extension (as COPY TABLE doesn't support 'merge' or 'upsert').
So my utility creates a foreign table and in a loop puts data into text file and upsert from this table (and commits).
Of course, I have to run my utility so that it creates a file somewhere Postgres can read from, but that's not a problem.
The speed improved from cca 2400 rows per second to 7000 (size of import text file varies, but I've limited it to 500k rows).