Postgres Pro
Downloads
Home   >   mailing lists

Thread: Catalog help

Catalog help

From
Scott Ribe
Date:
I've figured out how to use the catalogs to get from table name to name of
file(s) on disk, and also the toast files. But what I don't see is how to
handle the case where a cluster contains multiple databases with tables of
the same name--I fail to follow the oid keys somewhere along the way.

I don't actually have multiple tables with the same name (nor even multiple
databases in the cluster), I'm just trying to understand how to generalize
my query to correctly work in all cases.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Re: Catalog help

From
Erik Jones
Date:
On Oct 18, 2009, at 5:02 PM, Scott Ribe wrote:

> I've figured out how to use the catalogs to get from table name to
> name of
> file(s) on disk, and also the toast files. But what I don't see is
> how to
> handle the case where a cluster contains multiple databases with
> tables of
> the same name--I fail to follow the oid keys somewhere along the way.
>
> I don't actually have multiple tables with the same name (nor even
> multiple
> databases in the cluster), I'm just trying to understand how to
> generalize
> my query to correctly work in all cases.

Well, you can't.  Each database has it's own pg_class (and other)
catalog table and tables in any given database are not visible from
others.  If you mean table in multiple schemas with the same name then
you can differentiate on the pg_class.relnamespace field which is a
foreign key reference (although without an actual foreign key
constraint declared) to the oid values of rows in pg_namespace catalog
table.  For example, say you want to find all of the schemas with a
table named 'foo':

SELECT n.nspname
FROM pg_namespace n, pg_class c
WHERE c.relnamespace = n.oid
     AND c.relname = 'foo';

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

Re: Catalog help

From
Scott Ribe
Date:
> Well, you can't.  Each database has it's own pg_class (and other)
> catalog table and tables in any given database are not visible from
> others.

Ah well of course, that's the answer to my question. Only the entries for
the tables in the database to which I am currently connected are visible, so
there's no problem. (I didn't want to see other tables with the same name; I
wanted to avoid getting info on the wrong table.)

In hindsight, this should have been completely obvious. But I was looking at
the stuff regarding schemas and their ownership of tables, and got confused
thinking "yeah well, what about all the owning databases".

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice