Page 1 of 1

PostgreSQL ODBC error

Posted: Tue 14 Nov 2017 11:18
by Pet
Hello, today I downloaded Devart ODBC driver for PostgreSQL (, 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: ... )

Re: PostgreSQL ODBC error

Posted: Tue 14 Nov 2017 14:20
by azyk
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 ). Also include the scripts to create test tables in the sample.

Re: PostgreSQL ODBC error

Posted: Thu 16 Nov 2017 06:42
by Pet
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..

Re: PostgreSQL ODBC error

Posted: Thu 16 Nov 2017 07:24
by Pet
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.

Re: PostgreSQL ODBC error

Posted: Thu 16 Nov 2017 09:41
by azyk
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.

Re: PostgreSQL ODBC error

Posted: Tue 21 Nov 2017 06:02
by Pet
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).