Re: how to find index columns

While pg_catalog.pg_index has the create index script I otherwise cant
find the index columns in the information_schema.

Any ideas?

On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
> While pg_catalog.pg_index has the create index script I otherwise cant
> find the index columns in the information_schema.

That's because there are no index columns in the information_schema.
Indexes simply do not exist in SQL (they are merely an implementation
detail) and therefore are not included in information_schema (which is
defined by the SQL spec).

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Dec 5, 2006, at 8:43 PM, Andrew - Supernews wrote:
> On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
>> While pg_catalog.pg_index has the create index script I otherwise
>> cant
>> find the index columns in the information_schema.
>
> That's because there are no index columns in the information_schema.

I'm just a lonely lurker here and I never saw Timasmith's original
post -- only your response. Despite this sounding more like a -
general topic, here's the view I use:

CREATE VIEW information_schema.indexes AS
SELECT n.nspname AS schema_name,
c.relname AS table_name,
i.relname AS index_name,
substring(pg_get_indexdef(i.oid) FROM 'USING \\\\w+?
\\\\((.+?)\\\\)') AS column_names,
x.indisunique AS is_unique,
x.indisprimary AS is_pkey
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'::"char" AND i.relkind = 'i'::"char"
ORDER BY schema_name, table_name, is_pkey desc, is_unique
desc, index_name;

Sadly, I create it in the "information_schema". It probably doesn't
handle functional or partial indexes nicely and it is only known to
work with PG v8.1.x. Maybe this will inspire someone to expand upon it.

eric

Andrew - Supernews wrote:
> On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
> > While pg_catalog.pg_index has the create index script I otherwise cant
> > find the index columns in the information_schema.
>
> That's because there are no index columns in the information_schema.
> Indexes simply do not exist in SQL (they are merely an implementation
> detail) and therefore are not included in information_schema (which is
> defined by the SQL spec).
>
> --
> Andrew, Supernews
> http://www.supernews.com - individual and corporate NNTP services

So because it is not in the SQL spec you think that is a good reason
not to have indexes?

Or you think that we do not need that information in order to compare
to databases and create a DDL script to sync the two?

"Eric B. Ridge" wrote:
> On Dec 5, 2006, at 8:43 PM, Andrew - Supernews wrote:
> > On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
> >> While pg_catalog.pg_index has the create index script I otherwise
> >> cant
> >> find the index columns in the information_schema.
> >
> > That's because there are no index columns in the information_schema.
>
> I'm just a lonely lurker here and I never saw Timasmith's original
> post -- only your response. Despite this sounding more like a -
> general topic, here's the view I use:
>
> CREATE VIEW information_schema.indexes AS
> SELECT n.nspname AS schema_name,
> c.relname AS table_name,
> i.relname AS index_name,
> substring(pg_get_indexdef(i.oid) FROM 'USING \\\\w+?
> \\\\((.+?)\\\\)') AS column_names,
> x.indisunique AS is_unique,
> x.indisprimary AS is_pkey
> FROM pg_index x
> JOIN pg_class c ON c.oid = x.indrelid
> JOIN pg_class i ON i.oid = x.indexrelid
> LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind = 'r'::"char" AND i.relkind = 'i'::"char"
> ORDER BY schema_name, table_name, is_pkey desc, is_unique
> desc, index_name;
>
> Sadly, I create it in the "information_schema". It probably doesn't
> handle functional or partial indexes nicely and it is only known to
> work with PG v8.1.x. Maybe this will inspire someone to expand upon it.
>
> eric
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

The columns didnt show up in your query, likely due to an issue with
the regular expression.

I can of course write a script to extract the columns from a DDL chunk
of text create index propreitary code that appears to be stored in that
table.

Fundamentally everything in me screams program incorrectness, bug
inspiring, and just plain nastiness.

On 2006-12-06, "Eric B. Ridge" <ebr(at)tcdi(dot)com> wrote:
> On Dec 5, 2006, at 8:43 PM, Andrew - Supernews wrote:
>> On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
>>> While pg_catalog.pg_index has the create index script I otherwise
>>> cant
>>> find the index columns in the information_schema.
>>
>> That's because there are no index columns in the information_schema.
>
> I'm just a lonely lurker here and I never saw Timasmith's original
> post -- only your response. Despite this sounding more like a -
> general topic, here's the view I use:
[...]
> Sadly, I create it in the "information_schema". It probably doesn't
> handle functional or partial indexes nicely and it is only known to
> work with PG v8.1.x. Maybe this will inspire someone to expand upon it.

Way ahead of you: http://pgfoundry.org/projects/newsysviews/ (see the
source code repo, there are no releases)

(though that was written for 7.4 and 8.0, and needs some fixing for 8.1
still (though almost all of it works), and I haven't even tried it on 8.2
yet)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
> Andrew - Supernews wrote:
>> On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
>> > While pg_catalog.pg_index has the create index script I otherwise cant
>> > find the index columns in the information_schema.
>>
>> That's because there are no index columns in the information_schema.
>> Indexes simply do not exist in SQL (they are merely an implementation
>> detail) and therefore are not included in information_schema (which is
>> defined by the SQL spec).
>
> So because it is not in the SQL spec you think that is a good reason
> not to have indexes?
>
> Or you think that we do not need that information in order to compare
> to databases and create a DDL script to sync the two?

You seem to be mistaking the information_schema for something which
provides complete metadata - it does not, and due to the restrictions of
the SQL spec which defines it, it never can be complete.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Dec 5, 2006, at 11:17 PM, Andrew - Supernews wrote:

> Way ahead of you: http://pgfoundry.org/projects/newsysviews/ (see the
> source code repo, there are no releases)

Neat. I looked at this:

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/newsysviews/newsysview/
sql/indexes.sql?rev=1.2

Seems both views are missing the indexed column name(s). I ran into
a situation where I needed to present a list of all indexed columns
on a table, hence my "information_schema.indexes" view. It attempts
to parse the columns from the index definition. It just can't parse
definitions that use functional indexes.

Maybe you can consider adding a similar column to these views?
Output the column names as a name[] and maybe add another column for
"functional_definition"? Maybe Postgres could maintain that
information in the system catalogs, but I suppose there's a good
reason it doesn't already do that.

Just a thought.

eric

>
> (though that was written for 7.4 and 8.0, and needs some fixing for
> 8.1
> still (though almost all of it works), and I haven't even tried it
> on 8.2
> yet)
>
> --
> Andrew, Supernews
> http://www.supernews.com - individual and corporate NNTP services
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

Andrew - Supernews wrote:
> On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
> > Andrew - Supernews wrote:
> >> On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
> >> > While pg_catalog.pg_index has the create index script I otherwise cant
> >> > find the index columns in the information_schema.
> >>
> >> That's because there are no index columns in the information_schema.
> >> Indexes simply do not exist in SQL (they are merely an implementation
> >> detail) and therefore are not included in information_schema (which is
> >> defined by the SQL spec).
> >
> > So because it is not in the SQL spec you think that is a good reason
> > not to have indexes?
> >
> > Or you think that we do not need that information in order to compare
> > to databases and create a DDL script to sync the two?
>
> You seem to be mistaking the information_schema for something which
> provides complete metadata - it does not, and due to the restrictions of
> the SQL spec which defines it, it never can be complete.
>
> --
> Andrew, Supernews
> http://www.supernews.com - individual and corporate NNTP services

never is a long time. Never ever? Never ever can any database
administrator every know which columns are indexed by the database?
We can never ever issue any advice as to which columns are best to
query for?

We can only ever for the forseable future pull up the string create
index definition and interpret it manually or with a flawed regular
expression?

Nah, I dont buy it.

I didnt buy Postgresql either of course.

But I still love it, I will do a parsing script for now.

On 2006-12-06, "Eric B. Ridge" <ebr(at)tcdi(dot)com> wrote:
> On Dec 5, 2006, at 11:17 PM, Andrew - Supernews wrote:
>
>> Way ahead of you: http://pgfoundry.org/projects/newsysviews/ (see the
>> source code repo, there are no releases)
>
> Neat. I looked at this:
>
> http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/newsysviews/newsysview/
> sql/indexes.sql?rev=1.2
>
> Seems both views are missing the indexed column name(s). I ran into
> a situation where I needed to present a list of all indexed columns
> on a table, hence my "information_schema.indexes" view. It attempts
> to parse the columns from the index definition. It just can't parse
> definitions that use functional indexes.
>
> Maybe you can consider adding a similar column to these views?

Still way ahead of you: see the index_columns views.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
>
> Andrew - Supernews wrote:
>> You seem to be mistaking the information_schema for something which
>> provides complete metadata - it does not, and due to the restrictions of
>> the SQL spec which defines it, it never can be complete.
>
> never is a long time. Never ever? Never ever can any database
> administrator every know which columns are indexed by the database?

The database administrator can find out any time he likes by querying
the PG system catalogs (or views based on them, as the newsysviews ones
are). No need to parse the definition string.

"Why isn't there a view to show indexed columns" is a different question
to "why isn't there a view in information_schema to show indexed columns".

The answer to the former is "because after I did most of the work to
provide a complete set of system views, many developers expressed the
view that they would be pointless, and consequently I became
insufficiently motivated to finish them beyond what I myself use".

The answer to the latter is "because the definition of information_schema
is in the SQL spec, and it doesn't include indexes".

> But I still love it, I will do a parsing script for now.

http://pgfoundry.org/projects/newsysviews/ and look for index_columns.sql
in the CVS.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

>
> "Why isn't there a view to show indexed columns" is a different question
> to "why isn't there a view in information_schema to show indexed columns".
>
> The answer to the former is "because after I did most of the work to
> provide a complete set of system views, many developers expressed the
> view that they would be pointless, and consequently I became
> insufficiently motivated to finish them beyond what I myself use".

I am sorry for that, darn developers obviously arent performing their
own large scale database upgrades - I guess they left it up their DBA
to do it manually...

>
> The answer to the latter is "because the definition of information_schema
> is in the SQL spec, and it doesn't include indexes".
>
> > But I still love it, I will do a parsing script for now.
>
> http://pgfoundry.org/projects/newsysviews/ and look for index_columns.sql
> in the CVS.
>
> --
> Andrew, Supernews
> http://www.supernews.com - individual and corporate NNTP services

I did find it but I am struggling to pull it out and apply to 8.1
database on its own. The query and/or view creation has a dependency
on functions in

pg_sysviews

so it seems like I need the whole package.