Re: New Driver and Unique Indexes - Mailing list pgsql-hackers

Byron Nikolaidis wrote:
>
> 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.

Good! And thanks ;)!

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

as a quick (?) fix, can't this kind of query be identified in the driver
(now)
or in the backend(later) and rewritten to a union query like this

SELECT balance_id,company_id, balance_date, is_audited,comment,
balance_type, balance_filename  FROM balance
WHERE balance_id = 1 AND company_id=1
union
SELECT balance_id,company_id, balance_date, is_audited,comment,
balance_type, balance_filename  FROM balance
WHERE balance_id = 1 AND company_id=2
union
.
.
.
union
SELECT balance_id,company_id, balance_date, is_audited,comment,
balance_type, balance_filename  FROM balance
WHERE balance_id = 4 AND company_id=1
;

Or is the optimiser too smart and rewrites it back to the original form
?

once the identification phase is done in the backend, it should be
easy to check that all the fields ORed together are from an unique
index and do an index scan instead of a rewrite to union.

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

Actually it did not crash on me even on 3 keyparts, the backend just
grew to 97MB and stayed so until I closed access ;(.

Once  had to kill both access and backend, but then I had been
careless and viewed two tables with a primary key of more
than 1 field ;)

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

perhaps the rewriting of ORs to UNION could be done while "Preparing".

The heuristics would be just to check if the where clause has
altenating ANDs and ORs and then split and rewrite it to union at each
OR.

This of course can hit the infamous 8k limitation of query size
(is it still there ?)

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

As a temporary fix, you could just return the unique indexes of one
field only. You cold easyly remove the check later when backend gets
fixed.

or the ones with specific naming, for example ending in *_mspkey ?

>
> Sorry for the long length of this letter.
>

Until this is fixed it should go in some readme with BIG BOLD LETTERS ;)

Hannu