SELECT cls.oid, cls.relname as idxname, indrelid, indkey, ndisclustered, indisunique, indisprimary, n.nspname, proname, tab.relname as tabname, pn.nspname as pronspname, proargtypes, indclass, description, pg_get_expr((indpred, indrelid) as indconstraint, contype, ondeferrable, condeferred, amname
FROM pg_index idx
JOIN pg_class cls ON cls.oid=indexrelid
JOIN pg_class tab ON tab.oid=indrelid
JOIN pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_am am ON am.oid=cls.relam
LEFT OUTER JOIN pg_proc pr ON pr.oid=indproc
LEFT OUTER JOIN pg_namespace pn ON pn.oid=pr.pronamespace
LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid
LEFT OUTER JOIN pg_constraint con ON con.conrelid=indrelid AND conname=cls.relname
WHERE indrelid = 3742038::oid AND contype='p'
ORDER BY cls.relname;
(Note the double "(" in the SELECT.)
Debug log below.
Jochem
2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT c.oid, conname, consrc, condeferrable, condeferred, relname, nspname FROM pg_constraint c JOIN pg_class cl ON cl.oid=conrelid JOIN pg_namespace nl ON nl.oid=relnamespace WHERE contype = 'c' AND conrelid = 4337971::oid ORDER BY conname 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 INFO : Creating a pgCollection object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname, proname, tab.relname as tabname, pn.nspname as pronspname, proargtypes, indclass, description, pg_get_expr((indpred, indrelid) as indconstraint, contype, condeferrable, condeferred, amname FROM pg_index idx JOIN pg_class cls ON cls.oid=indexrelid JOIN pg_class tab ON tab.oid=indrelid JOIN pg_namespace n ON n.oid=tab.relnamespace JOIN pg_am am ON am.oid=cls.relam LEFT OUTER JOIN pg_proc pr ON pr.oid=indproc LEFT OUTER JOIN pg_namespace pn ON pn.oid=pr.pronamespace LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid LEFT OUTER JOIN pg_constraint con ON con.conrelid=indrelid AND conname=cls.relname WHERE indrelid = 4337971::oid AND conname IS NULL ORDER BY cls.relname 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 ERROR : ERROR: parser: parse error at or near "as" at character 254
2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 INFO : Creating a pgCollection object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT rw.oid, rw.ev_class, rulename, relname, nspname, description, is_instead, ev_type, ev_action, ev_qual, pg_get_ruledef(rw.oid) AS definition FROM pg_rewrite rw JOIN pg_class cl ON cl.oid=rw.ev_class JOIN pg_namespace nsp ON nsp.oid=cl.relnamespace LEFT OUTER JOIN pg_description des ON des.objoid=rw.oid WHERE ev_class = 4337971 ORDER BY rulename 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 INFO : Creating a pgCollection object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT t.oid, t.*, relname, nspname, des.description FROM pg_trigger t JOIN pg_class cl ON cl.oid=tgrelid JOIN pg_namespace na ON na.oid=relnamespace LEFT OUTER JOIN pg_description des ON des.objoid=t.oid WHERE NOT tgisconstraint AND tgrelid = 4337971::oid ORDER BY tgname 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT count(*) AS rows FROM jochemd_test.nulltest 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT c.relname , nspname FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhparent JOIN pg_namespace n ON n.oid=c.relnamespace WHERE i.inhrelid = 4337971::oid ORDER BY inhseqno 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 INFO : Displaying statistics for Table on jochemd_test 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT seq_scan AS "Sequential Scans", seq_tup_read AS "Sequential Tuples Read", idx_scan AS "Index Scans", idx_tup_fetch AS "Index Tuples Fetched", n_tup_ins AS "Tuples Inserted", n_tup_upd AS "Tuples Updated", n_tup_del AS "Tuples Deleted",heap_blks_read AS "Heap Blocks Read", heap_blks_hit AS "Heap Blocks Hit", idx_blks_read AS "Index Blocks Read", idx_blks_hit AS "Index Blocks Hit", toast_blks_read AS "Toast Blocks Read", toast_blks_hit AS "Toast Blocks Hit", tidx_blks_read AS "Toast Index Blocks Read", tidx_blks_hit AS "Toast Index Blocks Hit" FROM pg_stat_all_tables stat, pg_statio_all_tables statio WHERE stat.relid = statio.relid AND stat.relid = 4337971::oid 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 STATUS : Retrieving Table details... (2.63 secs) 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT indkey FROM pg_index WHERE indrelid=4337971::oid 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT conkey FROM pg_constraint ct JOIN pg_class cl on cl.oid=confrelid WHERE contype='f' AND conrelid = 4337971::oid ORDER BY conname 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT indkey FROM pg_index WHERE indrelid=4337971::oid 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT conkey FROM pg_constraint ct JOIN pg_class cl on cl.oid=confrelid WHERE contype='f' AND conrelid = 4337971::oid ORDER BY conname 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT indkey FROM pg_index WHERE indrelid=4337971::oid 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT conkey FROM pg_constraint ct JOIN pg_class cl on cl.oid=confrelid WHERE contype='f' AND conrelid = 4337971::oid ORDER BY conname 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 INFO : Destroying pgSet object 2003-07-01 13:21:02 QUERY : Set query (hector.oli.tudelft.nl:5432): SELECT cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname, proname, tab.relname as tabname, pn.nspname as pronspname, proargtypes, indclass, description, pg_get_expr((indpred, indrelid) as indconstraint, contype, condeferrable, condeferred, amname FROM pg_index idx JOIN pg_class cls ON cls.oid=indexrelid JOIN pg_class tab ON tab.oid=indrelid JOIN pg_namespace n ON n.oid=tab.relnamespace JOIN pg_am am ON am.oid=cls.relam LEFT OUTER JOIN pg_proc pr ON pr.oid=indproc LEFT OUTER JOIN pg_namespace pn ON pn.oid=pr.pronamespace LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid LEFT OUTER JOIN pg_constraint con ON con.conrelid=indrelid AND conname=cls.relname WHERE indrelid = 4337971::oid AND contype='p'
ORDER BY cls.relname 2003-07-01 13:21:02 INFO : Creating pgSet object 2003-07-01 13:21:02 ERROR : ERROR: parser: parse error at or near "as" at character 254
2003-07-01 13:21:04 INFO : Destroying pgSet object
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
