Thread: Querying database for table pk - better way?
I have a php application that needs to query the PK of a table - I'm
currently using this from the information_schema views:
SELECT column_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_name
AND tc.constraint_schema = ccu.constraint_schema
WHERE constraint_type = 'PRIMARY KEY'
AND tc.table_name = '$table'
AND tc.table_schema = '$schema'
This is an extremely slow query tho.
The top/bottom of my EXPLAIN ANALYZE (8.1.9):
Nested Loop (cost=22.20..342.24 rows=1 width=64) (actual
time=68985.008..68988.784 rows=1 loops=1)
Join Filter: ("inner".oid = "outer".relnamespace)
-> Nested Loop (cost=22.20..341.15 rows=1 width=68) (actual
time=68984.922..68988.694 rows=1 loops=1)
<snip about 60 rows>
Total runtime: 68989.637 ms
I can put the full explain output on the web if it's useful.
Curious if there is a better/cheaper way to get the data I'm looking
for though?
Thanks,
Josh
On 9/5/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> Curious if there is a better/cheaper way to get the data I'm looking
> for though?
SELECT conname
FROM pg_constraint c JOIN pg_class l ON c.conrelid = l.relfilenode
JOIN pg_namespace n ON n.OID = l.relnamespace
WHERE contype = 'p'
AND relname = '$table'
AND nspname = '$schema'
Sorry, just realized that I misread the query's requirements, but you can play with PG's system catalogs to complete it. See: http://www.postgresql.org/docs/8.1/static/catalogs.html
On 9/5/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> I have a php application that needs to query the PK of a table - I'm
> currently using this from the information_schema views:
try this:
CREATE OR REPLACE VIEW PKEYS AS
SELECT nspname as schema, c2.oid as tableoid, c2.relname as table,
substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) from
E'\\((.*)\\)')
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index i, pg_namespace n
WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND c.relkind = 'r'
AND i.indisprimary AND c.relnamespace = n.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
merlin
On Wed, 5 Sep 2007 19:08:33 -0400
"Merlin Moncure" <mmoncure@gmail.com> wrote:
> On 9/5/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> > I have a php application that needs to query the PK of a table -
> > I'm currently using this from the information_schema views:
>
> try this:
> CREATE OR REPLACE VIEW PKEYS AS
> SELECT nspname as schema, c2.oid as tableoid, c2.relname as
> table, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)
> from E'\\((.*)\\)')
> FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
> pg_catalog.pg_index i, pg_namespace n
> WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND
> c.relkind = 'r' AND i.indisprimary AND c.relnamespace = n.oid
> ORDER BY i.indisprimary DESC, i.indisunique DESC,
> c2.relname;
Beautiful, thanks - I had to change one thing - c2.relname is the
constraint name - c.relname is the table name.
Couple questions:
1.) The ORDER BY - I assume this can be reduced to "ORDER BY
c.relname"?
2.) Can you explain that substring line? What in the world is "from
E'\\((.*)\\)')" doing? Somehow it gets the column name....
3.) I changed the WHERE clause to use INNER JOIN's - is it just your
personal preference not to use INNER JOINs or does it actually have
an impact on the planner? I prefer to separate them so I can
visually keep the join conditions separate from the extra stuff in the
WHERE clause that filters the results.
My version (don't need the OID col):
CREATE OR REPLACE VIEW PKEYS (schema_name, table_name, column_name) AS
SELECT nspname, c.relname,
SUBSTRING(pg_catalog.pg_get_indexdef(i.indexrelid, 0, TRUE)
FROM E'\\((.*)\\)')
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
INNER JOIN pg_namespace n ON c.relnamespace = n.oid
INNER JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid
WHERE c.relkind = 'r' AND i.indisprimary
ORDER BY c.relname;
Thanks again,
Josh
Josh Trutwin wrote: > > SELECT nspname as schema, c2.oid as tableoid, c2.relname as > > table, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) > > from E'\\((.*)\\)') > 2.) Can you explain that substring line? What in the world is "from > E'\\((.*)\\)')" doing? Somehow it gets the column name.... The function is substring(foo from bar) so the E'' stuff you see is just the second operand. The E'' syntax allows backslash escapes; if you used '' without the E it would complain about the backslashes, because the SQL standard requires them to not mean escape characters, but in this case they are being treated as escapes. The first pair of backslashes are there to protect the parentheses from being turned them into capturing parens; they are doubled because the SQL parser eats one of them, so the regex engine really gets \((.*(\) which means "parens, then .*, then parens, but capture whatever is matched by the .*". Phew, I think this is a very confuse explanation. Hope it helps :-) -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio)
On 9/5/07, Josh Trutwin <josh@trutwins.homeip.net> wrote: > On Wed, 5 Sep 2007 19:08:33 -0400 > "Merlin Moncure" <mmoncure@gmail.com> wrote: > > > On 9/5/07, Josh Trutwin <josh@trutwins.homeip.net> wrote: > > > I have a php application that needs to query the PK of a table - > > > I'm currently using this from the information_schema views: > > > > try this: > > CREATE OR REPLACE VIEW PKEYS AS > > SELECT nspname as schema, c2.oid as tableoid, c2.relname as > > table, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) > > from E'\\((.*)\\)') > > FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, > > pg_catalog.pg_index i, pg_namespace n > > WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND > > c.relkind = 'r' AND i.indisprimary AND c.relnamespace = n.oid > > ORDER BY i.indisprimary DESC, i.indisunique DESC, > > c2.relname; > > Beautiful, thanks - I had to change one thing - c2.relname is the > constraint name - c.relname is the table name. right, my mistake... > Couple questions: > > 1.) The ORDER BY - I assume this can be reduced to "ORDER BY > c.relname"? actually, 'order by' in views, unless in subquery, is bad form and should be removed. I lifted a lot of this query by the way from psql...using the psql -E invocation to get the sql it makes when doing \d :-). > 3.) I changed the WHERE clause to use INNER JOIN's - is it just your > personal preference not to use INNER JOINs or does it actually have > an impact on the planner? I prefer to separate them so I can > visually keep the join conditions separate from the extra stuff in the Actually, I generally use joins (although I type just JOIN, not INNER JOIN) and prefer JOIN USING to JOIN ON where it can be used. For simple queries with no left or right joins or certain special cases I might use the where clause (it rarely makes a difference in the planner AFAIK). merlin