Skip site navigation (1) Skip section navigation (2)

SQLBulkOperations Segfault

From: Jeremy Faith <jfaith(at)cemsys(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: SQLBulkOperations Segfault
Date: 2008-04-18 15:59:57
Message-ID: 4808C57D.1080600@cemsys.com (view raw or flat)
Thread:
Lists: pgsql-odbc
Hi,

I have been experimenting with SQLBulkOperations to see if it is faster 
at inserting rows than using individual insert statements.
But when I run the attached(bo.c) example program it segfaults(note, 
this program works correctly with an Oracle ODBC connection).

The program requires the following table.
  create table customer
  (
    cust_num integer not null,
    first_name char(20),
    last_name char(20)
  );

Also the odb_db_open function call uses a hard coded DSN=pg_cdc_w, 
username=username and password=password.

I am using the latest versions of pgsql+postgres on Linux i.e.
  psqlodbc-08.03.0100
  postgresql-8.3.1

I turned on the ODBC debug log and added some log output to the driver 
and found that the segfault is occurring in the results.c:SC_pos_add 
function.

In particular the log shows
  POS ADD fi=(nil) ti=(nil)

and the segfault occurs on this line:-
                        if (*used != SQL_IGNORE && fi[i]->updatable)
So fi is NULL but is being referenced.

I did a bit more checking and found that parse_statement sets up 
stmt->ird->irdopts.fi i.e.
        if (SC_update_not_ready(stmt))
                parse_statement(s.stmt, TRUE);  /* not preferable */
so adding
  fi=stmt->ird->irdopts.fi;
after parse_statement gets past this segfault.

But then another segfault occurs in the results.c:positioned_load 
function as stmt->load_stmt is NULL
  so strlen(stmt->load_stmt) segfaults.

The code is complicated and I have not been able to determine how to fix 
this, it seems likely to me that even the fi fix may be covering up an 
earlier problem somewhere else in the code.

Does anybody use SQLBulkOperations to load data?
If so is it faster than individual inserts?

The postgres driver included with unixODBC fails as well but it at least 
return a standard ODBC error so I suppose it doesn't support 
SQLBulkOperations, I understand the unixODBC driver is defunct anyway.

I have noticed that the unixODBC postgres driver is significantly 
quicker when using a prepared insert to insert 100,000 rows. 
Specifically psqlodbc takes about 75% longer, but this is reduced to 
about 45% longer when 'UseServerSidePrepare = 1' is set. Indeed it was 
this speed difference that prompted me to try SQLBulkOperation in the 
first place.
Are there any other setting that may improve the speed of the psqlodbc 
driver?
Any ideas why the old unixODBC driver is so much faster?

Regards,
Jeremy Faith

Attachment: bo.c
Description: text/x-csrc (5.2 KB)

Responses

pgsql-odbc by date

Next:From: Jeff CrumbleyDate: 2008-04-18 20:15:31
Subject: 32-bit ODBC Linked Server from 64 bit SQL Server
Previous:From: Jeff CrumbleyDate: 2008-04-17 14:51:07
Subject: Call Postgres function from Linked Server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group