Thread: Missing data in Information Schema

George Weaver <gweaver@shaw.ca> writes:
> This morning while trying to generate a file of metadata I found that =
> most of the views in Information_Schema are empty, for example =
> referential_constraints, table_constraints, etc.

The information_schema is mostly restricted to show you info about
objects you own --- if you run your test as a superuser, do you get
more info?

(This behavior is per SQL:1999.  We recently noticed that SQL:2003 seems
to be laxer and allow you to see info about objects you can access, not
just those you own.  We'll probably fix PG 8.2 to follow that behavior,
but it's too late for 8.1.)

            regards, tom lane

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "George Weaver" <gweaver@shaw.ca>
Cc: <pgsql-novice@postgresql.org>
Sent: Wednesday, November 02, 2005 12:23 PM
Subject: Re: [NOVICE] Missing data in Information Schema


> George Weaver <gweaver@shaw.ca> writes:
>> This morning while trying to generate a file of metadata I found that =
>> most of the views in Information_Schema are empty, for example =
>> referential_constraints, table_constraints, etc.
>
> The information_schema is mostly restricted to show you info about
> objects you own --- if you run your test as a superuser, do you get
> more info?

I was logged in as a superuser (postgres).  Most of the schema were owned by
postgres but some containing stored functions were not, and most of the
tables in the public schema were owned by another user.  Based on your
comments, I dumped the database with the No Owner option and restored it to
a test database logged in as postgres.  All the objects in the test database
are owned by postgres and now the views in information_schema are showing
the expected information.

Thanks for your (prompt) help, Tom.

George

> (This behavior is per SQL:1999.  We recently noticed that SQL:2003 seems
> to be laxer and allow you to see info about objects you can access, not
> just those you own.  We'll probably fix PG 8.2 to follow that behavior,
> but it's too late for 8.1.)
>
> regards, tom lane
>