Re: [INTERFACES] new odbc fails

John J. Boris, Sr. wrote:

> I downloaded and installed the new odbc driver. I am running WIndows NT 4.0
> and my PostreSQL is on a LINUX box. I can connect to the database using MS
> Access 97, link the tables, it allows me to select unique keys for each
> table but when I try to view a table I get an ODBC failed message and the
> table is filled with NAME#.
>

This is most likely because of the backend canonifier problem.  Check your
psqlodbc.log (enabled with the CommLog driver option) file to see if there is
an error message from the backend concerning "palloc failure, memory
exhausted".

The unique key you are specifying in Access can result in "keyset" queries of
the form:

select from table where (k1 = 1 AND k2 = 1 AND k3 = 1) OR (k1 = 1 AND k2 = 1
AND k3 = 2) ......... (k1 = 1 and k2 = 1 and k3 = 10)

Access usually uses a rowset size of 10, thus you will have 10 groups of ORs.
Depending on how many parts your key is made up of, your AND groupings may look
differently than I have shown it.

Bottom line is, a fix for the backend is being worked on in two different
capacities.  One fix, which we use here, is a patch, written by Dave Hartwig,
which rewrites the statements into UNIONS in the backend.  This is called the
KSQO patch (KeySet Query Optimization, enabled with the set ksqo to 'ON'
command, which you can put in the Connect Settings driver/datasource options).
I'm not sure if we have this patch on our website or if it will be included in
Postgres 6.4.  You may want to check the website and inquire about it.

Also, Bruce Momjian  is trying to fix the problem in the backend.  I'm not sure
of its current status or if it will be complete in Postgres 6.4.

Byron