Thread: Re: how to find index columns
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
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
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
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
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
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
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
> > "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.
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.
"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.
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?