Обсуждение: Re: how to find index columns

Поиск
Список
Период
Сортировка

Re: how to find index columns

От
Andrew - Supernews
Дата:
On 2006-12-06, Timasmith <timasmith@hotmail.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


Re: how to find index columns

От
"Eric B. Ridge"
Дата:
On Dec 5, 2006, at 8:43 PM, Andrew - Supernews wrote:
> On 2006-12-06, Timasmith <timasmith@hotmail.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
LEFTJOIN 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




Re: how to find index columns

От
Andrew - Supernews
Дата:
On 2006-12-06, "Eric B. Ridge" <ebr@tcdi.com> wrote:
> On Dec 5, 2006, at 8:43 PM, Andrew - Supernews wrote:
>> On 2006-12-06, Timasmith <timasmith@hotmail.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


Re: how to find index columns

От
Andrew - Supernews
Дата:
On 2006-12-06, Timasmith <timasmith@hotmail.com> wrote:
> Andrew - Supernews wrote:
>> On 2006-12-06, Timasmith <timasmith@hotmail.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


Re: how to find index columns

От
"Eric B. Ridge"
Дата:
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



Re: how to find index columns

От
Andrew - Supernews
Дата:
On 2006-12-06, "Eric B. Ridge" <ebr@tcdi.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


Re: how to find index columns

От
Andrew - Supernews
Дата:
On 2006-12-06, Timasmith <timasmith@hotmail.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


Re: how to find index columns

От
"Timasmith"
Дата:
>
> "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.



Re: how to find index columns

От
"Timasmith"
Дата:
Andrew - Supernews wrote:
> On 2006-12-06, Timasmith <timasmith@hotmail.com> wrote:
> > Andrew - Supernews wrote:
> >> On 2006-12-06, Timasmith <timasmith@hotmail.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.



Re: how to find index columns

От
"Timasmith"
Дата:
"Eric B. Ridge" wrote:
> On Dec 5, 2006, at 8:43 PM, Andrew - Supernews wrote:
> > On 2006-12-06, Timasmith <timasmith@hotmail.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.



Re: how to find index columns

От
"Timasmith"
Дата:
Andrew - Supernews wrote:
> On 2006-12-06, Timasmith <timasmith@hotmail.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?