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

Filtering ODBC table list

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: PostgreSQL ODBC List <pgsql-odbc(at)postgresql(dot)org>
Subject: Filtering ODBC table list
Date: 2007-10-03 12:57:09
Message-ID: 20071003125709.GB5031@tamriel.snowman.net (view raw or flat)
Thread:
Lists: pgsql-odbc
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... <Shrug>).

  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

Responses

pgsql-odbc by date

Next:From: Obe, ReginaDate: 2007-10-03 13:15:10
Subject: Re: Filtering ODBC table list
Previous:From: Dmitry SamokhinDate: 2007-10-02 10:21:53
Subject: Re: Unable to update JOIN'ed recordset with PSQLODBC 08.02.0400 and ADO

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