Re: gin/gist indexes show twice

Le 28/03/2011 12:10, Timon a écrit :
> 2011/3/28 Guillaume Lelarge <guillaume@lelarge.info>:
>> Le 28/03/2011 10:57, Timon a écrit :
>>> yup, seem that problem is not with git/gist indexes.
>>> I have found the query for this grid in file
>>> pgadmin/schema/pgIndex.cpp line 670.
>>>
>>> I run this query in Sql Query tool and get same results:
>>> SELECT
>>>       indexrelname,
>>>       idx_scan, idx_tup_read, idx_tup_fetch,
>>>       pg_size_pretty(pg_relation_size(indexrelid)),
>>>       refclassid, refobjid, deptype,
>>>       refclassid::regclass, refobjid::regclass
>>> FROM pg_stat_all_indexes stat
>>>       JOIN pg_class cls ON cls.oid=indexrelid
>>>       LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid
>>> = cls.oid AND dep.refobjsubid = '0')
>>> --    LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid
>>> AND con.oid = dep.refobjid)
>>> WHERE schemaname = 'public'
>>>       AND
>>>       stat.relname = 'user_attr_text'
>>>       --AND con.contype IS NULL
>>> ORDER BY indexrelname
>>>
>>
>> I'm wondering why you commented the left join on pg_constraint. It could
>> be your issue.
>> pgAdmin doesn't have these lines commented, and, AFAICT, it works. See
>>
http://git.postgresql.org/gitweb?p=pgadmin3.git;a=blob;f=pgadmin/schema/pgIndex.cpp;h=4f270eedc03fd9e8fd3033ec627ca304462ff7bd;hb=HEAD#l670.
>
> I know it. I just want to pay attention on pg_depend. idx_fts has 2
> dependencies - pg_ts_config;11335, pg_class;user_attr_text, and no
> constraint dependencies. That's why it shows twitce.
>
> my quick ugly patch:
> diff --git a/pgadmin/schema/pgIndex.cpp b/pgadmin/schema/pgIndex.cpp
> index 4f270ee..29abcfd 100644
> --- a/pgadmin/schema/pgIndex.cpp
> +++ b/pgadmin/schema/pgIndex.cpp
> @@ -676,7 +676,7 @@ void pgIndexBaseCollection::ShowStatistics(frmMain
> *form, ctlListView *statistic
>         sql += wxT("\n")
>                wxT("  FROM pg_stat_all_indexes stat\n")
>                wxT("  JOIN pg_class cls ON cls.oid=indexrelid\n")
> -              wxT("  LEFT JOIN pg_depend dep ON (dep.classid =
> cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')\n")
> +              wxT("  LEFT JOIN pg_depend dep ON (dep.classid =
> cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND
> refclassid::regclass::varchar = 'pg_constraint')\n")
>                wxT("  LEFT OUTER JOIN pg_constraint con ON
> (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)\n")
>                wxT("  WHERE schemaname = ") +
> qtDbString(GetTable()->GetSchema()->GetName())
>                + wxT(" AND stat.relname = ") + qtDbString(GetTable()->GetName())
>

Can you share with us a small testcase? I mean some SQL DDL statements
that would helps us reproduce the issue? Thanks.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com