Re: pgstattuple TODO item

Dave Page a écrit :
> Guillaume Lelarge wrote:
>> OK. I first chose to use a menu because executing pgstattuple on a big
>> table (more than a gig) is really slow.
>
> Hmm, OK. In that case, perhaps it should remain, and append the
> additional data to the stats tab having selected, and populated it as
> normal first. If the tab is already shown, then it can just append the
> data of course.
>
> I think 'Get extended statistics' or similar might be a better name for
> the option though - best not to use the cryptic function name.
>

This is exactly what I did. You'll find attached my new patch.

Regards.


--
Guillaume.
<!-- http://abs.traduc.org/
     http://lfs.traduc.org/
     http://docs.postgresqlfr.org/ -->
Index: pgadmin/include/schema/pgTable.h
===================================================================
--- pgadmin/include/schema/pgTable.h    (révision 5851)
+++ pgadmin/include/schema/pgTable.h    (copie de travail)
@@ -86,10 +86,13 @@
     bool CanRestore() { return true; }
     bool WantDummyChild() { return true; }
     bool GetCanHint();
+    bool GetShowExtendedStatistics() { return showExtendedStatistics; }
+    void iSetShowExtendedStatistics(bool b) { showExtendedStatistics = b; }

     bool HasStats() { return true; }
     bool HasDepends() { return true; }
     bool HasReferences() { return true; }
+    bool HasPgstattuple();

     wxMenu *GetNewMenu();
     wxString GetSql(ctlTree *browser);
@@ -107,7 +110,7 @@
     void AppendStuff(wxString &sql, ctlTree *browser, pgaFactory &factory);
     wxULongLong rows;
     double estimatedRows;
-    bool hasOids, hasSubclass, rowsCounted, isReplicated;
+    bool hasOids, hasSubclass, rowsCounted, isReplicated, showExtendedStatistics;
     long inheritedTableCount;
     wxString quotedInheritedTables, inheritedTables, primaryKey, quotedPrimaryKey,
         primaryKeyName, primaryKeyColNumbers, tablespace;
Index: pgadmin/include/utils/pgfeatures.h
===================================================================
--- pgadmin/include/utils/pgfeatures.h    (révision 5851)
+++ pgadmin/include/utils/pgfeatures.h    (copie de travail)
@@ -22,6 +22,7 @@
     FEATURE_POSTMASTER_STARTTIME,
     FEATURE_TERMINATE_BACKEND,
     FEATURE_RELOAD_CONF,
+    FEATURE_PGSTATTUPLE,
     FEATURE_LAST
 };

Index: pgadmin/include/dlg/dlgTable.h
===================================================================
--- pgadmin/include/dlg/dlgTable.h    (révision 5851)
+++ pgadmin/include/dlg/dlgTable.h    (copie de travail)
@@ -82,4 +82,13 @@
     bool CheckEnable(pgObject *obj);
 };

+
+class executePgstattupleFactory : public contextActionFactory
+{
+public:
+    executePgstattupleFactory(menuFactoryList *list, wxMenu *mnu, wxToolBar *toolbar);
+    wxWindow *StartDialog(frmMain *form, pgObject *obj);
+    bool CheckEnable(pgObject *obj);
+};
+
 #endif
Index: pgadmin/frm/frmMain.cpp
===================================================================
--- pgadmin/frm/frmMain.cpp    (révision 5851)
+++ pgadmin/frm/frmMain.cpp    (copie de travail)
@@ -257,6 +257,7 @@
     viewMenu->AppendSeparator();
     actionFactory *refFact=new refreshFactory(menuFactories, viewMenu, toolBar);
     new countRowsFactory(menuFactories, viewMenu, 0);
+    new executePgstattupleFactory(menuFactories, viewMenu, 0);


     //--------------------------
Index: pgadmin/db/pgConn.cpp
===================================================================
--- pgadmin/db/pgConn.cpp    (révision 5851)
+++ pgadmin/db/pgConn.cpp    (copie de travail)
@@ -370,6 +370,28 @@
             }
             delete set;
         }
+
+        sql=
+            wxT("SELECT proname, pronargs, proargtypes[0] AS arg0\n")
+            wxT("  FROM pg_proc\n")
+            wxT(" WHERE proname = 'pgstattuple'\n");
+
+        set=ExecuteSet(sql);
+
+        if (set)
+        {
+            while (!set->Eof())
+            {
+                wxString proname=set->GetVal(wxT("proname"));
+                long pronargs = set->GetLong(wxT("pronargs"));
+
+                if (proname == wxT("pgstattuple") && pronargs == 1 && set->GetLong(wxT("arg0")) == 25)
+                    features[FEATURE_PGSTATTUPLE]= true;
+
+                set->MoveNext();
+            }
+            delete set;
+        }
     }

     if (featureNo <= FEATURE_INITIALIZED || featureNo >= FEATURE_LAST)
Index: pgadmin/schema/pgTable.cpp
===================================================================
--- pgadmin/schema/pgTable.cpp    (révision 5851)
+++ pgadmin/schema/pgTable.cpp    (copie de travail)
@@ -34,6 +34,7 @@
 {
     inheritedTableCount=0;
     rowsCounted = false;
+    showExtendedStatistics = false;
 }

 pgTable::~pgTable()
@@ -636,6 +637,12 @@
 }


+bool pgTable::HasPgstattuple()
+{
+    return GetConnection()->HasFeature(FEATURE_PGSTATTUPLE);
+}
+
+
 ///////////////////////////////////////////////////////////


@@ -690,6 +697,7 @@

         delete stats;
     }
+
 }


@@ -721,12 +729,31 @@
             +  wxT(", CASE WHEN cl.reltoastrelid = 0 THEN ") + qtDbString(_("none")) + wxT(" ELSE
pg_size_pretty(pg_relation_size(cl.reltoastrelid)+COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index
WHEREindrelid=cl.reltoastrelid)::int8, 0)) END AS ") + qtIdent(_("Toast Table Size")) 
             +  wxT(", pg_size_pretty(COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE
indrelid=stat.relid)::int8,0)) AS ") + qtIdent(_("Indexes Size")); 
     }
+
+    if (showExtendedStatistics)
+    {
+        sql += wxT("\n")
+               wxT(", tuple_count AS ") + qtIdent(_("Tuple Count")) + wxT(",\n")
+               wxT("  pg_size_pretty(tuple_len) AS ") + qtIdent(_("Tuple Length")) + wxT(",\n")
+               wxT("  tuple_percent AS ") + qtIdent(_("Tuple Percent")) + wxT(",\n")
+               wxT("  dead_tuple_count AS ") + qtIdent(_("Dead Tuple Count")) + wxT(",\n")
+               wxT("  pg_size_pretty(dead_tuple_len) AS ") + qtIdent(_("Dead Tuple Length")) + wxT(",\n")
+               wxT("  dead_tuple_percent AS ") + qtIdent(_("Dead Tuple Percent")) + wxT(",\n")
+               wxT("  pg_size_pretty(free_space) AS ") + qtIdent(_("Free Space")) + wxT(",\n")
+               wxT("  free_percent AS ") + qtIdent(_("Free Percent")) + wxT("\n")
+               wxT("  FROM pgstattuple('") + GetQuotedFullIdentifier() + wxT("'), pg_stat_all_tables stat");
+    }
+    else
+    {
+        sql += wxT("\n")
+               wxT("  FROM pg_stat_all_tables stat");
+    }
     sql +=  wxT("\n")
-        wxT("  FROM pg_stat_all_tables stat\n")
         wxT("  JOIN pg_statio_all_tables statio ON stat.relid = statio.relid\n")
         wxT("  JOIN pg_class cl ON cl.oid=stat.relid\n")
         wxT(" WHERE stat.relid = ") + GetOidStr();

+
     DisplayStatistics(statistics, sql);
 }

Index: pgadmin/dlg/dlgTable.cpp
===================================================================
--- pgadmin/dlg/dlgTable.cpp    (révision 5851)
+++ pgadmin/dlg/dlgTable.cpp    (copie de travail)
@@ -1171,3 +1171,27 @@
 {
     return obj && obj->IsCreatedBy(tableFactory);
 }
+
+
+executePgstattupleFactory::executePgstattupleFactory(menuFactoryList *list, wxMenu *mnu, wxToolBar *toolbar) :
contextActionFactory(list)
+{
+    mnu->Append(id, _("&Get extended statistics"), _("Get extended statistics via pgstattuple on the selected
object."));
+}
+
+
+wxWindow *executePgstattupleFactory::StartDialog(frmMain *form, pgObject *obj)
+{
+    ((pgTable*)obj)->iSetShowExtendedStatistics(true);
+
+    wxTreeItemId item=form->GetBrowser()->GetSelection();
+    if (obj == form->GetBrowser()->GetObject(item))
+        obj->ShowTreeDetail(form->GetBrowser(), 0, form->GetProperties());
+
+    return 0;
+}
+
+
+bool executePgstattupleFactory::CheckEnable(pgObject *obj)
+{
+    return obj && obj->IsCreatedBy(tableFactory) && ((pgTable*)obj)->HasPgstattuple();
+}