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

New Driver and Unique Indexes

From: Byron Nikolaidis <byronn(at)insightdist(dot)com>
To: "pgsql-interfaces(at)postgreSQL(dot)org" <pgsql-interfaces(at)postgreSQL(dot)org>
Cc: Sbragion Denis <infotecn(at)tin(dot)it>, "pgsql-hackers(at)postgreSQL(dot)org" <pgsql-hackers(at)postgreSQL(dot)org>, Olaf Mittelstaedt <mstaedt(at)va-sigi(dot)va(dot)fh-ulm(dot)de>, Hannu Krosing <hannu(at)trust(dot)ee>
Subject: New Driver and Unique Indexes
Date: 1998-04-27 19:05:11
Message-ID: 3544D6E7.3211AAB4@insightdist.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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


Responses

pgsql-hackers by date

Next:From: Bruce StephensDate: 1998-04-27 19:19:11
Subject: Re: [HACKERS] Re: [QUESTIONS] Practical SQL Handbook - demo script for postgreSQL
Previous:From: Jackson, DeJuanDate: 1998-04-27 17:34:53
Subject: RE: [HACKERS] Re: [QUESTIONS] Practical SQL Handbook - demo script for postgreSQL

pgsql-interfaces by date

Next:From: Vadim B. MikheevDate: 1998-04-28 01:57:31
Subject: Re: [HACKERS] New Driver and Unique Indexes
Previous:From: David HartwigDate: 1998-04-27 16:54:08
Subject: Re: [INTERFACES] Odbc and Visual Basic

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