PostgreSQL ODBC error

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
Pet
Posts: 4
Joined: Tue 14 Nov 2017 08:55

PostgreSQL ODBC error

Post by Pet » Tue 14 Nov 2017 11:18

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 )

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: PostgreSQL ODBC error

Post by azyk » Tue 14 Nov 2017 14:20

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.

Pet
Posts: 4
Joined: Tue 14 Nov 2017 08:55

Re: PostgreSQL ODBC error

Post by Pet » Thu 16 Nov 2017 06:42

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..

Pet
Posts: 4
Joined: Tue 14 Nov 2017 08:55

Re: PostgreSQL ODBC error

Post by Pet » Thu 16 Nov 2017 07:24

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.

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: PostgreSQL ODBC error

Post by azyk » Thu 16 Nov 2017 09:41

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.

Pet
Posts: 4
Joined: Tue 14 Nov 2017 08:55

Re: PostgreSQL ODBC error

Post by Pet » Tue 21 Nov 2017 06:02

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).

Post Reply