I am using Byron's ODBC driver (version .0239 downloaded today) with
Access 7.00 under Win95 with Postgres 6.3.  I am trying to link tables
into Access.  Most tables work fine, but any field with the name 'name'
or 'sortname' or even 'garbagename' may not be used as part of an
index.  This occurs whether the index is picked up automatically by the
driver, or if you are asked to choose a unique field by Access.

Can this be fixed?  As a workaround, could the driver optionally not
tell the client application about indices?  In this way, I could tell
Access to ignore them and then (I think) I would be able to get at my
data.

Ewan Mellor.

Tue, 12 May 1998 11:49:57 +0100
Ewan Mellor <em@nexus.co.uk>
MS Access & PsqlODBC: Invalid field name 'name'
Set the "Recognize Unique Indexes" to disabled (unchecked).
Then, when Access asks you for a unique field, don't select anything and
hit ok.
Thus, you are telling access you have no index.

This should allow you to get at your data until we figure out what "name"
has to do with this problem.

Byron

Ewan Mellor wrote:

> I am using Byron's ODBC driver (version .0239 downloaded today) with
> Access 7.00 under Win95 with Postgres 6.3.  I am trying to link tables
> into Access.  Most tables work fine, but any field with the name 'name'
> or 'sortname' or even 'garbagename' may not be used as part of an
> index.  This occurs whether the index is picked up automatically by the
> driver, or if you are asked to choose a unique field by Access.
>
> Can this be fixed?  As a workaround, could the driver optionally not
> tell the client application about indices?  In this way, I could tell
> Access to ignore them and then (I think) I would be able to get at my
> data.
>
> Ewan Mellor.




Tue, 12 May 1998 10:34:38 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
Ewan Mellor wrote:
> 
> I am using Byron's ODBC driver (version .0239 downloaded today) with
> Access 7.00 under Win95 with Postgres 6.3.  I am trying to link tables
> into Access.  Most tables work fine, but any field with the name 'name'
> or 'sortname' or even 'garbagename' may not be used as part of an
> index.  This occurs whether the index is picked up automatically by the
> driver, or if you are asked to choose a unique field by Access.

Starting from v2.0 of Access the worn "name" became kind of reserved 
word in Access, as the table itself aquired an _attribute_ name, which 
contains the name of the table.

so having a field called name is a problem in anyway (for Access).

I have no idea why "sortname" or "garbagename" does not work.

> Can this be fixed?  As a workaround, could the driver optionally not
> tell the client application about indices?  In this way, I could tell
> Access to ignore them and then (I think) I would be able to get at my
> data.

you can still do 

ALTER TABLE yourtable RENAME name TO not_name_any_more;

-------------

Hannu

Tue, 12 May 1998 17:53:05 +0300
Hannu Krosing <hannu@trust.ee>
Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
Hannu Krosing wrote:
> 
> Ewan Mellor wrote:
> >
> > I am using Byron's ODBC driver (version .0239 downloaded today) with
> > Access 7.00 under Win95 with Postgres 6.3.  I am trying to link tables
> > into Access.  Most tables work fine, but any field with the name 'name'
> > or 'sortname' or even 'garbagename' may not be used as part of an
> > index.  This occurs whether the index is picked up automatically by the
> > driver, or if you are asked to choose a unique field by Access.
> 
> Starting from v2.0 of Access the worn "name" became kind of reserved
> word in Access, as the table itself aquired an _attribute_ name, which
> contains the name of the table.
> 
> so having a field called name is a problem in anyway (for Access).

So it's a "we're Microsoft and we can do what we want" reserved word,
and not an "internationally recognised standard SQL" reserved word :-(

> I have no idea why "sortname" or "garbagename" does not work.
> 
> > Can this be fixed?  As a workaround, could the driver optionally not
> > tell the client application about indices?  In this way, I could tell
> > Access to ignore them and then (I think) I would be able to get at my
> > data.
> 
> you can still do
> 
> ALTER TABLE yourtable RENAME name TO not_name_any_more;

I'd rather not - there is a reasonable amount of code sitting on top of
this DB :-(

Thanks a lot for your help,

Ewan.

Tue, 12 May 1998 17:08:58 +0100
Ewan Mellor <em@nexus.co.uk>
Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
Byron Nikolaidis wrote:
> 
> Set the "Recognize Unique Indexes" to disabled (unchecked).
> Then, when Access asks you for a unique field, don't select anything and
> hit ok.
> Thus, you are telling access you have no index.
> 
> This should allow you to get at your data until we figure out what "name"
> has to do with this problem.

I tried that, but it does not seem to help.  Perhaps Access is asking
for the index information of its own accord?

I have just discovered it also objects to:

rt_url,
url,
genre,
name_en,
stored,
stored1,
address, and
server.

Note that many have succeeded.  Curiouser and curiouser...

Thanks, Byron, both for your help and for what looks like it will be a
really useful driver.  If only the whole planet didn't use Access...

> Byron
> 
> Ewan Mellor wrote:
> 
> > I am using Byron's ODBC driver (version .0239 downloaded today) with
> > Access 7.00 under Win95 with Postgres 6.3.  I am trying to link tables
> > into Access.  Most tables work fine, but any field with the name 'name'
> > or 'sortname' or even 'garbagename' may not be used as part of an
> > index.  This occurs whether the index is picked up automatically by the
> > driver, or if you are asked to choose a unique field by Access.
> >
> > Can this be fixed?  As a workaround, could the driver optionally not
> > tell the client application about indices?  In this way, I could tell
> > Access to ignore them and then (I think) I would be able to get at my
> > data.
> >
> > Ewan Mellor.

Tue, 12 May 1998 17:24:12 +0100
Ewan Mellor <em@nexus.co.uk>
Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
Ewan Mellor wrote:

> Byron Nikolaidis wrote:
> >
> > Set the "Recognize Unique Indexes" to disabled (unchecked).
> > Then, when Access asks you for a unique field, don't select anything and
> > hit ok.
> > Thus, you are telling access you have no index.
> >
> > This should allow you to get at your data until we figure out what "name"
> > has to do with this problem.
>
> I tried that, but it does not seem to help.  Perhaps Access is asking
> for the index information of its own accord?
>
> I have just discovered it also objects to:
>
> rt_url,
> url,
> genre,
> name_en,
> stored,
> stored1,
> address, and
> server.
>

Wait, I think I have it!

Access will not allow you to index on LongVarchar data types OR character types
that are longer than 254 characters (255 with null).  I bet these columns you
are having trouble with are Postgres TEXT types or varchars/chars that are over
254.

Check out the odbc driver setup options dialog.  You can map TEXT fields to
plain varchar; then set the LongVarChar size to 254, and it should work!

Byron



Tue, 12 May 1998 13:00:47 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
Byron Nikolaidis wrote:
> 
> Ewan Mellor wrote:
> 
> > Byron Nikolaidis wrote:
> > >
> > > Set the "Recognize Unique Indexes" to disabled (unchecked).
> > > Then, when Access asks you for a unique field, don't select anything and
> > > hit ok.
> > > Thus, you are telling access you have no index.
> > >
> > > This should allow you to get at your data until we figure out what "name"
> > > has to do with this problem.
> >
> > I tried that, but it does not seem to help.  Perhaps Access is asking
> > for the index information of its own accord?
> >
> > I have just discovered it also objects to:
> >
> > rt_url,
> > url,
> > genre,
> > name_en,
> > stored,
> > stored1,
> > address, and
> > server.
> >
> 
> Wait, I think I have it!
> 
> Access will not allow you to index on LongVarchar data types OR character types
> that are longer than 254 characters (255 with null).  I bet these columns you
> are having trouble with are Postgres TEXT types or varchars/chars that are over
> 254.
> 
> Check out the odbc driver setup options dialog.  You can map TEXT fields to
> plain varchar; then set the LongVarChar size to 254, and it should work!

Well done indeed!  With that you have elevated yourself to the higher
echelons of gurudom.  Congratulations. :-)

One for the FAQ methinks...

Ewan.

Tue, 12 May 1998 19:17:35 +0100
Ewan Mellor <em@nexus.co.uk>
Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
On Tue, 12 May 1998, Ewan Mellor wrote:

> Hannu Krosing wrote:
> > 
> > Ewan Mellor wrote:
> > >
> > > I am using Byron's ODBC driver (version .0239 downloaded today) with
> > > Access 7.00 under Win95 with Postgres 6.3.  I am trying to link tables
> > > into Access.  Most tables work fine, but any field with the name 'name'
> > > or 'sortname' or even 'garbagename' may not be used as part of an
> > > index.  This occurs whether the index is picked up automatically by the
> > > driver, or if you are asked to choose a unique field by Access.
> > 
I'm using Byron's ODBC v6.30.0238 with M$-Access-97 under Win95 with
PostgreSQL v6.3. I can successful link tables into M$-Access even if they have
the word 'name' as column name or table name.
M$-Access picked column 'name' as unique index and it seems work. I can
read and write data into may table named 'gname'.
this is my example:

odbc=> create table gname ( name name, pname int);
CREATE
odbc=> insert into gname values ( 'name',1234);
INSERT 528554 1
odbc=> select * from gname;
name|pname
----+-----
name| 1234
(1 row)

odbc=> \d gname

Table    = gname
+-------------------------------+----------------------------------+-------+
|           Field               |              Type                | Length|
+-------------------------------+----------------------------------+-------+
| name                          | name                             |    32 |
| pname                         | int4                             |     4 |
+-------------------------------+----------------------------------+-------+
                                                                Jose'


Wed, 13 May 1998 10:13:20 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
Hi,

   I'm using PostgreSQL-6.3 / psqlodbc 06.30.0242 / M$-Access97.
I created a REPORT with a leftjoin that takes a lot of time.
There are 3850 rows in the main table.
PostgreSQL takes about..............: 960 secs to print all records.
The same test using MySQL takes only:  85 secs and the same
test using M$-Access takes about....:  45 secs.
I configured ODBC drive to write the log file to sees what ODBC is doing
but seems that it writes log file only while fetching rows.
Is there a way to know what ODBC is doing. To know why it takes so long time?
                                                           Thanks, Jose'


Thu, 28 May 1998 10:50:19 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
ODBC is slow with M$-Access Report


Jose' Soares Da Silva wrote:

> Hi,
>
>    I'm using PostgreSQL-6.3 / psqlodbc 06.30.0242 / M$-Access97.
> I created a REPORT with a leftjoin that takes a lot of time.
> There are 3850 rows in the main table.
> PostgreSQL takes about..............: 960 secs to print all records.
> The same test using MySQL takes only:  85 secs and the same
> test using M$-Access takes about....:  45 secs.

This is never a simple comparison.   Performance using Access/PostgreSQL can be
greatly effected by the driver settings.   In particular, if you tell MS Access
that there is a unique index on a table, at link time, or to "Recognize Unique
Indexed"  (and there is one), Access will generate queries which the backend will
not respond to very optimally.   Especially where outer joins are concerned.
These queries are characterized by numerous OR(s).    Unfortunately under these
conditions the backend does make use of the very index that Access is trying to
take advantage of.

So relinking the table without Access's recognition of the primary key (unique
index) may help performance.  The down side is that you may not modify a table
from Access without a specified primary key.

There is also another  factor.  Does MySql support outer joins?   PostgreSQL does
not at this time.   MS Access will hide this fact from the users and perform the
join within Access.  Thus, creating the situation described above.





> I configured ODBC drive to write the log file to sees what ODBC is doing
> but seems that it writes log file only while fetching rows.
> Is there a way to know what ODBC is doing. To know why it takes so long time?
>                                                            Thanks, Jose'

The CommLog was created to log SQL statement  communication with the server.   A
much more detailed log can be activated from the "ODBC Data Source Administrator"
dialog under the "Tracing" tab.  If you use this feature you may want to clear it
out first.   It will also bring processing to a craw.


Thu, 28 May 1998 10:06:43 -0400
David Hartwig <daveh@insightdist.com>
Re: [INTERFACES] ODBC is slow with M$-Access Report
We are working on a project that IMHO give more prestige to
PostgreSQL.
The Hygea project concern the use of an Unix-like Operating  sys-
tem  as  "back-end" of a Client M$-windows application connected
by ODBC that will be installed in about 80 Italian Helth Depart-
ments for the veterinary controls and prevention.
Therefore...

O.S.: We choose Linux for his proved reliability.

Client: We choose to develop the Client with M$-Access because we
need (unfortunately) a complete integration with Micro$oft World.

Database: We choose PostgreSQL for his reliability  and  for  his
compatibility with SQL/92 standard recommendation and for his ex-
cellent technical support provided by "The PostgreSQL Development
Team" and his mailing lists.

Nevertheless  the  union  among M$-Access and PostgreSQL is quite
suffered for the following reasons:

1. The PostgreSQL doesn't use the index with  "OR"  operator  and
so is not possible to define a multiple key to use with M$-Access
and we need to retreat using OID as primary keys (thanks to Byron
Nikolaidis and David Hartwig of insightdist.com that are doing a
really great job with ODBC driver), but with the obvious consequences.

2.  As PostgreSQL doesn't allow an "ORDER BY" on columns not
included in the target list of the "SELECT", (I know that it is
SQL/92 standard, but IMO it's a fool thing), therefore, is not possible
to  have the "dynaset "sorted for any field that is different from
the key (in our case the useless OIDs).

3. The times required to run complex reports (for example those that
include LEFT JOINS) is very long (about 15 minutes to retrieve
2850 rows).

We hope the PostgreSQL next release v6.4 may have some of these features
otherwise, we have to give up the project.

> Jose' Soares Da Silva wrote:
> 
> > Hi,
> >
> >    I'm using PostgreSQL-6.3 / psqlodbc 06.30.0242 / M$-Access97.
> > I created a REPORT with a leftjoin that takes a lot of time.
> > There are 3850 rows in the main table.
> > PostgreSQL takes about..............: 960 secs to print all records.
> > The same test using MySQL takes only:  85 secs and the same
> > test using M$-Access takes about....:  45 secs.
> 
> This is never a simple comparison.   Performance using Access/PostgreSQL can be
> greatly effected by the driver settings.   In particular, if you tell MS Access
> that there is a unique index on a table, at link time, or to "Recognize Unique
> Indexed"  (and there is one), Access will generate queries which the backend will
> not respond to very optimally.   Especially where outer joins are concerned.
> These queries are characterized by numerous OR(s).    Unfortunately under these
> conditions the backend does make use of the very index that Access is trying to
> take advantage of.
> 
> So relinking the table without Access's recognition of the primary key (unique
> index) may help performance.  The down side is that you may not modify a table
> from Access without a specified primary key.
> 
> There is also another  factor.  Does MySql support outer joins?   PostgreSQL does
> not at this time.   MS Access will hide this fact from the users and perform the
> join within Access.  Thus, creating the situation described above.
> 
> > I configured ODBC drive to write the log file to sees what ODBC is doing
> > but seems that it writes log file only while fetching rows.
> > Is there a way to know what ODBC is doing. To know why it takes so long time?
> >                                                            Thanks, Jose'
> 
> The CommLog was created to log SQL statement  communication with the server.   A
> much more detailed log can be activated from the "ODBC Data Source Administrator"
> dialog under the "Tracing" tab.  If you use this feature you may want to clear it
> out first.   It will also bring processing to a craw.

                                  |  |
~~~~~~~~~~~~~~~~~~~~~~~~          |  |            ~~~~~~~~~~~~~~~~~~~~~~~~
          Progetto HYGEA      ----    ----        www.sferacarta.com
    Sfera Carta Software      ----    ----        sferac@bo.nettuno.it
       Via Bazzanese, 69          |  |            Fax. ++39 51 6131537
Casalecchio R.(BO) Italy          |  |            Tel. ++39 51  591054
-----------------------------------------------------------------------------


Mon, 1 Jun 1998 17:42:26 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] ODBC is slow with M$-Access Report


Jose' Soares Da Silva wrote:

> We are working on a project that IMHO give more prestige to
> PostgreSQL.
> The Hygea project concern the use of an Unix-like Operating  sys-
> tem  as  "back-end" of a Client M$-windows application connected
> by ODBC that will be installed in about 80 Italian Helth Depart-
> ments for the veterinary controls and prevention.
> Therefore...

> O.S.: We choose Linux for his proved reliability.
>
> Client: We choose to develop the Client with M$-Access because we
> need (unfortunately) a complete integration with Micro$oft World.
>
> Database: We choose PostgreSQL for his reliability  and  for  his
> compatibility with SQL/92 standard recommendation and for his ex-
> cellent technical support provided by "The PostgreSQL Development
> Team" and his mailing lists.
>
> Nevertheless  the  union  among M$-Access and PostgreSQL is quite
> suffered for the following reasons:
>
> 1. The PostgreSQL doesn't use the index with  "OR"  operator  and
> so is not possible to define a multiple key to use with M$-Access
> and we need to retreat using OID as primary keys (thanks to Byron
> Nikolaidis and David Hartwig of insightdist.com that are doing a
> really great job with ODBC driver), but with the obvious consequences.

 I am currently working on a solution as time will allow.   Hopefully part of 6.4

>
>
> 2.  As PostgreSQL doesn't allow an "ORDER BY" on columns not
> included in the target list of the "SELECT", (I know that it is
> SQL/92 standard, but IMO it's a fool thing), therefore, is not possible
> to  have the "dynaset "sorted for any field that is different from
> the key (in our case the useless OIDs).
>

This fix is in alpha and will be in the 6.4 release.  I do not know when 6.4 is slated
for release, but I am willing to send you a patch if it is critical for you to proceed.

> 3. The times required to run complex reports (for example those that
> include LEFT JOINS) is very long (about 15 minutes to retrieve
> 2850 rows).
>

The solution to your first item will resolve this also.

> We hope the PostgreSQL next release v6.4 may have some of these features
> otherwise, we have to give up the project.
>
> > Jose' Soares Da Silva wrote:
> >
> > > Hi,
> > >
> > >    I'm using PostgreSQL-6.3 / psqlodbc 06.30.0242 / M$-Access97.
> > > I created a REPORT with a leftjoin that takes a lot of time.
> > > There are 3850 rows in the main table.
> > > PostgreSQL takes about..............: 960 secs to print all records.
> > > The same test using MySQL takes only:  85 secs and the same
> > > test using M$-Access takes about....:  45 secs.
> >
> > This is never a simple comparison.   Performance using Access/PostgreSQL can be
> > greatly effected by the driver settings.   In particular, if you tell MS Access
> > that there is a unique index on a table, at link time, or to "Recognize Unique
> > Indexed"  (and there is one), Access will generate queries which the backend will
> > not respond to very optimally.   Especially where outer joins are concerned.
> > These queries are characterized by numerous OR(s).    Unfortunately under these
> > conditions the backend does make use of the very index that Access is trying to
> > take advantage of.
> >
> > So relinking the table without Access's recognition of the primary key (unique
> > index) may help performance.  The down side is that you may not modify a table
> > from Access without a specified primary key.
> >
> > There is also another  factor.  Does MySql support outer joins?   PostgreSQL does
> > not at this time.   MS Access will hide this fact from the users and perform the
> > join within Access.  Thus, creating the situation described above.
> >
> > > I configured ODBC drive to write the log file to sees what ODBC is doing
> > > but seems that it writes log file only while fetching rows.
> > > Is there a way to know what ODBC is doing. To know why it takes so long time?
> > >                                                            Thanks, Jose'
> >
> > The CommLog was created to log SQL statement  communication with the server.   A
> > much more detailed log can be activated from the "ODBC Data Source Administrator"
> > dialog under the "Tracing" tab.  If you use this feature you may want to clear it
> > out first.   It will also bring processing to a craw.
>


Tue, 02 Jun 1998 11:47:31 -0400
David Hartwig <daveh@insightdist.com>
Re: [INTERFACES] ODBC is slow with M$-Access Report
> 
> We are working on a project that IMHO give more prestige to
> PostgreSQL.
> The Hygea project concern the use of an Unix-like Operating  sys-
> tem  as  "back-end" of a Client M$-windows application connected
> by ODBC that will be installed in about 80 Italian Helth Depart-
> ments for the veterinary controls and prevention.
> Therefore...
> 
> O.S.: We choose Linux for his proved reliability.
> 
> Client: We choose to develop the Client with M$-Access because we
> need (unfortunately) a complete integration with Micro$oft World.
> 
> Database: We choose PostgreSQL for his reliability  and  for  his
> compatibility with SQL/92 standard recommendation and for his ex-
> cellent technical support provided by "The PostgreSQL Development
> Team" and his mailing lists.

Great.

> 
> Nevertheless  the  union  among M$-Access and PostgreSQL is quite
> suffered for the following reasons:
> 
> 1. The PostgreSQL doesn't use the index with  "OR"  operator  and
> so is not possible to define a multiple key to use with M$-Access
> and we need to retreat using OID as primary keys (thanks to Byron
> Nikolaidis and David Hartwig of insightdist.com that are doing a
> really great job with ODBC driver), but with the obvious consequences.

Yes, we need to work on this.  I am sure performance really suffers
because of this.  Vadim, is this on your short list?

> 
> 2.  As PostgreSQL doesn't allow an "ORDER BY" on columns not
> included in the target list of the "SELECT", (I know that it is
> SQL/92 standard, but IMO it's a fool thing), therefore, is not possible
> to  have the "dynaset "sorted for any field that is different from
> the key (in our case the useless OIDs).

David at Insight just added this, so it certainly will be in 6.4.

> 
> 3. The times required to run complex reports (for example those that
> include LEFT JOINS) is very long (about 15 minutes to retrieve
> 2850 rows).

Yea, we need this too.  Not sure where we are with this.  Can you give
an example?



-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tue, 2 Jun 1998 12:21:48 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [INTERFACES] ODBC is slow with M$-Access Report
On Tue, 2 Jun 1998, David Hartwig wrote:

> > O.S.: We choose Linux for his proved reliability.

	*quiet snicker*

> > 2.  As PostgreSQL doesn't allow an "ORDER BY" on columns not
> > included in the target list of the "SELECT", (I know that it is
> > SQL/92 standard, but IMO it's a fool thing), therefore, is not possible
> > to  have the "dynaset "sorted for any field that is different from
> > the key (in our case the useless OIDs).
> >
> 
> This fix is in alpha and will be in the 6.4 release.  I do not know when
> 6.4 is slated for release, but I am willing to send you a patch if it is
> critical for you to proceed. 

	6.4 is slated for Oct 1st...we had thought Sep 1st, except that,
being the tail end of the summer, alot of ppl tend to be in limbo 



Tue, 2 Jun 1998 12:34:40 -0400 (EDT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [INTERFACES] ODBC is slow with M$-Access Report
David Hartwig wrote:
> 
> > 1. The PostgreSQL doesn't use the index with  "OR"  operator  and
> > so is not possible to define a multiple key to use with M$-Access
> > and we need to retreat using OID as primary keys (thanks to Byron
> > Nikolaidis and David Hartwig of insightdist.com that are doing a
> > really great job with ODBC driver), but with the obvious consequences.
> 
>  I am currently working on a solution as time will allow.   Hopefully part of 6.4
> 

Will this solution be in ODBC driver (rewrite ORs to UNION) or in 
the backend (fix the optimiser)?

------
Hannu

Tue, 02 Jun 1998 20:11:41 +0300
Hannu Krosing <hannu@trust.ee>
Re: [INTERFACES] ODBC is slow with M$-Access Report


Hannu Krosing wrote:

> David Hartwig wrote:
> >
> > > 1. The PostgreSQL doesn't use the index with  "OR"  operator  and
> > > so is not possible to define a multiple key to use with M$-Access
> > > and we need to retreat using OID as primary keys (thanks to Byron
> > > Nikolaidis and David Hartwig of insightdist.com that are doing a
> > > really great job with ODBC driver), but with the obvious consequences.
> >
> >  I am currently working on a solution as time will allow.   Hopefully part of 6.4
> >
>
> Will this solution be in ODBC driver (rewrite ORs to UNION) or in
> the backend (fix the optimiser)?
>

The short answer is that the rewrite on the driver side is problematic.

I had hoped to be further along with my feasibility research before raising the issue
for again discussion.  But, now is as good a time as any.  Let me first clarify the
problem for any ppl jumping into the middle of this thread.

Many general purpose database clients applications such as MS Access routinely generate
queries with the following signature:

SELECT k1, k2, k3, a4, a5, ...  FROM t WHERE
    (k1 = const01 AND k2 = const02 AND k3 = const03)  OR
    (k1 = const11 AND k2 = const12 AND k3 = const13)  OR
    (k1 = const21 AND k2 = const22 AND k3 = const23)  OR
    (k1 = const31 AND k2 = const32 AND k3 = const33)  OR
    (k1 = const41 AND k2 = const42 AND k3 = const43)  OR
    (k1 = const51 AND k2 = const52 AND k3 = const53)  OR
    (k1 = const61 AND k2 = const62 AND k3 = const63)  OR
    (k1 = const71 AND k2 = const72 AND k3 = const73)  OR
    (k1 = const81 AND k2 = const82 AND k3 = const73)  OR
    (k1 = const91 AND k2 = const92 AND k3 = const93);

Where k(n) id is the attribute for a multi-part primary key and const(m)(n) is any
constant.

Performance on this kind of a query is crucial to these client side tools.  These are
used to maneuver through large tables without having to slurp in the entire table.
Currently the backend optimizer tries to arrange the WHERE clause into conjunctive
normal form (cnfify()).  Unfortunatley this process leads to memory exhaustion.

I have come up with 3 methods of attacking the problem.

1.   As Mr. Krosing mentioned we could rewrite the query on the driver side. before
sending it to the backend.    One could identify the signature of such a query and upon
verification replace all the ORs with a "UNION SELECT k1, k2, k3, a4, ... FROM t
WHERE"    I have tested this substitution with up to 30 OR groupings and it performs
like a charm.   Thanks Bruce.  Here is the kicker.  If you do some guestimations using
a table with say 50 attributes, you will see that very quickly you will be bumping into
the 8K message limit.   I am finding that this is not unusual in our user community.

2.  Use a similar strategy to the first method except you do the rewrite the query in
the backend; some where after parsing and before optimizations.   The exact location
can be debated.   The basic idea is to pre-qualify the rewrite by requiring only one
table, no subselects, no unions, etc.   Then, identify the AND/OR signature in the
qualifier expression tree.   For each OR grouping, clone the main query tree (minus the
qualifier clause) onto a list of query trees hanging off the UNION structure element.
All the while, pruning the OR nodes off and attaching them to the cloned query tree.
The code required by this approach is very isolated and should be low risk as a
result.   My concern is that this approach is too narrow and does not fit well into the
long term goals of the project.   My guess is that performance will be even better than
the first method.

3.  Get out of the way and let Vadim do his thing.

Comments?


Tue, 02 Jun 1998 14:43:14 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Re: [INTERFACES] ODBC is slow with M$-Access Report
> 3.  Get out of the way and let Vadim do his thing.
> 
> Comments?

Yes, I have queried him to find out where this sits on his list.  It
would be intestesting to what, if anything, he has planned for 6.4.  I
think I have forgotten.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tue, 2 Jun 1998 17:52:17 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [INTERFACES] ODBC is slow with M$-Access Report
On Tue, 2 Jun 1998, David Hartwig wrote:

<DELETED>
> > Nikolaidis and David Hartwig of insightdist.com that are doing a
> > really great job with ODBC driver), but with the obvious consequences.
> 
>  I am currently working on a solution as time will allow.   Hopefully part of 6.4
> > 2.  As PostgreSQL doesn't allow an "ORDER BY" on columns not
> > included in the target list of the "SELECT", (I know that it is
> > SQL/92 standard, but IMO it's a fool thing), therefore, is not possible
> > to  have the "dynaset "sorted for any field that is different from
> > the key (in our case the useless OIDs).
> 
> This fix is in alpha and will be in the 6.4 release.  I do not know when 6.4 is slated
> for release, but I am willing to send you a patch if it is critical for you to proceed.
> 
> > 3. The times required to run complex reports (for example those that
> > include LEFT JOINS) is very long (about 15 minutes to retrieve
> > 2850 rows).
> >
> The solution to your first item will resolve this also.
> 
This is a great new David, Thank you very much for your work, this allow us
to go on with this important project.
For us, is enough to know that it will be available *maybe* on next release.
                                               Thanks a lot, Jose'


Wed, 3 Jun 1998 10:48:06 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] ODBC is slow with M$-Access Report
On Tue, 2 Jun 1998, The Hermit Hacker wrote:

> On Tue, 2 Jun 1998, David Hartwig wrote:
> 
> > > O.S.: We choose Linux for his proved reliability.
> 
> 	*quiet snicker*
If I understand, this mean incredibility.
We are using Linux since 1994 and we are satisfied. ;-)
> > > 2.  As PostgreSQL doesn't allow an "ORDER BY" on columns not
> > > included in the target list of the "SELECT", (I know that it is
> > > SQL/92 standard, but IMO it's a fool thing), therefore, is not possible
> > > to  have the "dynaset "sorted for any field that is different from
> > > the key (in our case the useless OIDs).
> > >
> > 
> > This fix is in alpha and will be in the 6.4 release.  I do not know when
> > 6.4 is slated for release, but I am willing to send you a patch if it is
> > critical for you to proceed. 
> 
> 	6.4 is slated for Oct 1st...we had thought Sep 1st, except that,
> being the tail end of the summer, alot of ppl tend to be in limbo 
Thank you,
                                         Jose'


Wed, 3 Jun 1998 11:02:06 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [HACKERS] Re: [INTERFACES] ODBC is slow with M$-Access Report
On Tue, 2 Jun 1998, Bruce Momjian wrote:

> > 3. The times required to run complex reports (for example those that
> > include LEFT JOINS) is very long (about 15 minutes to retrieve
> > 2850 rows).
> 
> Yea, we need this too.  Not sure where we are with this.  Can you give
> an example?
Our problem is linked with using sub-reports, for now we solved this problem 
using queries instead of sub-reports and it works well.
                                                          Thanks any way,
							       Jose'


Thu, 4 Jun 1998 11:59:59 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [HACKERS] Re: [INTERFACES] ODBC is slow with M$-Access Report