Filtering ODBC table list

--l76fUT7nc3MelDdI
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

We've run into a couple of issues that, in general, I think would be
helped greatly by having some way to filter the list of tables which
are shown in the ODBC link/import list. I'm curious if that is
something which is possible to implement, the difficulty involved, and
if anyone would be interested in working on it.

Basically, it's like this:

#1: Too many tables. If you have more than (I think) 65,535 tables in
the database, access breaks. What happens is that if you pick a table
beyond that point, it just tries to pull in all tables past that
limit. For example, 70,000 tables, user picks table 67,000 (and
*just* that table in the ODBC link list) then access will try and
link/import all tables from 65,536 -> 70,000. This is kind of a
problem. I might be off on the exact numbers, but I don't think so.

#2: Permissions issues. Showing tables that a user doesn't have
access to is, at best, frustrating. Even just filtering based on
usage rights on schemas would be an improvement, if not going the full
way to relation-level permission checks. It really strikes me that
that's not a terribly difficult thing to check or incorporate into a
query that pulls the table info.

#3: Slow. It takes *forever* on a gigabit link w/ a good desktop box
and a fast-as-hell server to get a full table list (see the 70k tables
example above for the number of tables we're talking about here). I
don't know what the latency is from but I suspect it's from Access
getting the table definitions and whatnot for every table. The server
doesn't seem busy at all while this is going on. I'm really hopeful
that some kind of filtering mechanism would improve this situation
greatly by only pulling down and getting the info for those tables
which someone is interested in/has access to.

What I would like to see, honestly, is two things: A config toggle to
say "only show schemas/tables I have access to" and some way to say,
preferrably w/o changing some "config" option, "show me only tables in
this schema" or "show me only tables in schemas that match this
regexp". I realize there are limitations from Access and the ODBC
interface that might make these things impossible to do, if so, I'd
really like to hear about them now so I can think about how I could
implement the same thing through some other means (like making a bunch
of different databases or something; I'd hate it, but if there's no
other way... ).

Comments? Thoughts? I might be able to get some time to work on this
myself, but I'd really like to hear what other people think or if
anyone else is interested in working on it or already has some
solution before spending resources on it.

Thanks,

Stephen

--l76fUT7nc3MelDdI
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHA5GlrzgMPqB3kigRAmYcAJ49lWu4+eATw4aEd+YPsKmQplKLnQCg gh1E
1koM8U21Ke6M6I/GJ/lMLJI=
=2kUC
-----END PGP SIGNATURE-----

--l76fUT7nc3MelDdI--

Re: Filtering ODBC table list

I think 2 is already done in the latest ODBC driver. I installed it=0D
recently on a client pc and was surprised that I didn't see the table I=0D
had just created as a link option when trying to link via Access and=0D
then after adding permissions to the table in PostgreSQL, I then saw it=0D
in the list.=0D
=0D
Hope that helps,=0D
Regina=0D
=0D
-----Original Message-----=0D
From: pgsql-odbc-owner@postgresql.org=0D
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Stephen Frost=0D
Sent: Wednesday, October 03, 2007 8:57 AM=0D
To: PostgreSQL ODBC List=0D
Subject: [ODBC] Filtering ODBC table list=0D
=0D
Greetings,=0D
=0D
We've run into a couple of issues that, in general, I think would be=0D
helped greatly by having some way to filter the list of tables which=0D
are shown in the ODBC link/import list. I'm curious if that is=0D
something which is possible to implement, the difficulty involved, and=0D
if anyone would be interested in working on it.=0D
=0D
Basically, it's like this:=0D
=0D
#1: Too many tables. If you have more than (I think) 65,535 tables in=0D
the database, access breaks. What happens is that if you pick a table=0D
beyond that point, it just tries to pull in all tables past that=0D
limit. For example, 70,000 tables, user picks table 67,000 (and=0D
*just* that table in the ODBC link list) then access will try and=0D
link/import all tables from 65,536 -> 70,000. This is kind of a=0D
problem. I might be off on the exact numbers, but I don't think so.=0D
=0D
#2: Permissions issues. Showing tables that a user doesn't have=0D
access to is, at best, frustrating. Even just filtering based on=0D
usage rights on schemas would be an improvement, if not going the full=0D
way to relation-level permission checks. It really strikes me that=0D
that's not a terribly difficult thing to check or incorporate into a=0D
query that pulls the table info.=0D
=0D
#3: Slow. It takes *forever* on a gigabit link w/ a good desktop box=0D
and a fast-as-hell server to get a full table list (see the 70k tables=0D
example above for the number of tables we're talking about here). I=0D
don't know what the latency is from but I suspect it's from Access=0D
getting the table definitions and whatnot for every table. The server=0D
doesn't seem busy at all while this is going on. I'm really hopeful=0D
that some kind of filtering mechanism would improve this situation=0D
greatly by only pulling down and getting the info for those tables=0D
which someone is interested in/has access to.=0D
=0D
What I would like to see, honestly, is two things: A config toggle to=0D
say "only show schemas/tables I have access to" and some way to say,=0D
preferrably w/o changing some "config" option, "show me only tables in=0D
this schema" or "show me only tables in schemas that match this=0D
regexp". I realize there are limitations from Access and the ODBC=0D
interface that might make these things impossible to do, if so, I'd=0D
really like to hear about them now so I can think about how I could=0D
implement the same thing through some other means (like making a bunch=0D
of different databases or something; I'd hate it, but if there's no=0D
other way... ).=0D
=0D
Comments? Thoughts? I might be able to get some time to work on this=0D
myself, but I'd really like to hear what other people think or if=0D
anyone else is interested in working on it or already has some=0D
solution before spending resources on it.=0D
=0D
Thanks,=0D
=0D
Stephen=0D
=0D
-----------------------------------------=0D
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended=0D
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.=0D


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Filtering ODBC table list

On Wed, 2007-10-03 at 09:15 -0400, Obe, Regina wrote:
> I think 2 is already done in the latest ODBC driver. I installed it
> recently on a client pc and was surprised that I didn't see the table I
> had just created as a link option when trying to link via Access and
> then after adding permissions to the table in PostgreSQL, I then saw it
> in the list.
>
> Hope that helps,
> Regina


Hi Regina/Steve,

I posted a patch for #2 here:
http://archives.postgresql.org/pgsql-odbc/2007-06/msg00028.p hp, although
if you continue reading the following discussions, the patch was
rejected as-is. Inoue-san, did a version of this patch get applied to
CVS?


Kind regards,

Mark.

--
ILande - Open Source Consultancy
http://www.ilande.co.uk



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match