New Driver and Unique Indexes - Mailing list pgsql-interfaces

Hello,

I have posted a new version of the ODBC driver at our web site.
(http://www.insightdist.com/psqlodbc).  We are also now including a
version number (this one is 06.30.0010).  You can click on this link and
see what changes this version includes.  Also, you can look with the
ODBC Administrator under "ODBC Drivers" and get the current version for
correspondence and so forth.

1. This new version fixes problems with execution time parameters
(SQLParamData, SQLPutData) for text fields where parameters were being
dropped and a '?' was appearing in the query.

2. Also, functionality has been added to return information about UNIQUE
INDEXES. This was never implemented in the old driver (it assumed
Postgres couldn't have any).    This should allow Access 2.0 users to be
allowed to update records.  Also, it should allow Visual Basic to do
updates.

---------  HACKERS INVITED TO PLEASE READ THIS SECTION ---------

One downside about UNIQUE INDEXES however, is how Microsoft Access
handles them when you open the table in datasheet view.  Whether you
specify the unique index at link time, or the driver provides the info,
Access will try to use queries which show up a problem with the backend:

Here is an example of an Access query with a unique index on a single
field:

SELECT balance_id,company_id, balance_date, is_audited,comment,
balance_type, balance_filename  FROM balance  WHERE balance_id = 1 OR
balance_id = 2 OR balance_id = 3 OR balance_id = 4 OR balance_id = 5 OR
balance_id = 6 OR balance_id = 7 OR balance_id = 8 OR balance_id = 9 OR
balance_id = 10

The more keyparts you have, the worse the problem is (2 keyparts):

SELECT balance_id,company_id, balance_date, is_audited,comment,
balance_type, balance_filename  FROM balance  WHERE balance_id = 1 AND
company_id=1 OR balance_id = 1 AND company_id=2 OR balance_id = 1 AND
company_id=3 OR balance_id = 2 AND company_id=1 OR balance_id = 2 AND
company_id=2 OR balance_id = 2 AND company_id=3 OR balance_id = 3 AND
company_id=1 OR balance_id = 3 AND company_id=2 OR balance_id = 3 AND
company_id=3 OR balance_id = 4 AND company_id=1

Any more than 2 keyparts, results in crashing the backend with the
message
"palloc failure:  memory exhausted".  Even at 2 keyparts, performance
suffers greatly.

In both of the above examples, Access is trying to retrieve 10 records
using a "Prepared" statement  (prepared statementents are "faked" in the
driver, since they are not implemented in the backend)  with the unique
index of the table.

We have known about this problem and have discussed it with the hackers
list in the past.  It is on the todo list under "Performance" and it
appears as
"Allow indexes to be used with OR clauses(Vadim) ".  I am not sure of
the priority of this fix, however, or how difficult it would be to
implement it.

The reason we are mentioning this with renewed vigor, is that in the
past, with the old driver, Access 7.0 and Access 97, would ask the user
what they wanted the unique index to be.  You could tell it whatever you
wanted, and even, not specify any unique index.  Now, with this new
unique index fix, you will not have a choice as to whether you want to
use unique indexes or not, which, depending on how many fields are being
indexed on, may crash the backend.

Of course, if you are not using "unique" indexes on your table, Access
7.0 and 97 will ask you at link time, as before.

Does anyone have any knowledge of the above problem and/or the priority
of the fix that Vadim is mentioned on?

Sorry for the long length of this letter.

Regards,

Byron