pg_stats view added to CVS tip - Mailing list pgsql-hackers

Per previous discussions, I have modified initdb to revoke public read
access on pg_statistic (you can still read it if you're superuser,
of course) and instead added a view pg_stats, which will show the
statistics rows only for tables that current_user has read access to.
This uses the has_table_privilege function just contributed by Joe
Conway.

The view also tries to be slightly more user-friendly than a raw look at
pg_statistic.  It joins to pg_class and pg_attribute to get the names of
tables and columns, and it presents the various kinds of statistics
intelligently labeled.  (Thus, the view has wired into it exactly the
sort of assumptions we agreed not to make in pg_statistic itself.
Peter E. may be unhappy with me here.  But it seems to me that we can
change/extend the view definition when we add new kinds of statistics
entries.)

The actual definition looks like

regression=# \d pg_stats           View "pg_stats"    Attribute     |  Type   | Modifier
-------------------+---------+----------tablename         | name    |attname           | name    |null_frac         |
real   |avg_width         | integer |n_distinct        | real    |most_common_vals  | text[]  |most_common_freqs |
real[] |histogram_bounds  | text[]  |correlation       | real    |
 
View definition: SELECT c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width,
s.stadistinctAS n_distinct, CASE WHEN (1 = s.stakind1) THEN s.stavalues1 WHEN (1 = s.stakind2) THEN s.stavalues2 WHEN
(1= s.stakind3) THEN s.stavalues3 WHEN (1 = s.stakind4) THEN s.stavalues4 ELSE NULL::"_text" END AS most_common_vals,
CASEWHEN (1 = s.stakind1) THEN s.stanumbers1 WHEN (1 = s.stakind2) THEN s.stanumbers2 WHEN (1 = s.stakind3) THEN
s.stanumbers3WHEN (1 = s.stakind4) THEN s.stanumbers4 ELSE NULL::"_float4" END AS most_common_freqs, CASE WHEN (2 =
s.stakind1)THEN s.stavalues1 WHEN (2 = s.stakind2) THEN s.stavalues2 WHEN (2 = s.stakind3) THEN s.stavalues3 WHEN (2 =
s.stakind4)THEN s.stavalues4 ELSE NULL::"_text" END AS histogram_bounds, CASE WHEN (3 = s.stakind1) THEN
s.stanumbers1[1]WHEN (3 = s.stakind2) THEN s.stanumbers2[1] WHEN (3 = s.stakind3) THEN s.stanumbers3[1] WHEN (3 =
s.stakind4)THEN s.stanumbers4[1] ELSE NULL::float4 END AS correlat!
 
ion FROM pg_class c, pg_attribute a, pg_statistic s WHERE ((((c.oid = s.starelid) AND (c.oid = a.attrelid)) AND
(a.attnum= s.staattnum)) AND has_table_privilege(c.oid, 'select'::text));
 

and a sample result is:

regression=# select * from pg_stats where tablename = 'tenk1' and attname = 'unique1';tablename | attname | null_frac |
avg_width| n_distinct | most_common_vals | most_common_freqs |                              histogram_bounds
                 | correlation
 

-----------+---------+-----------+-----------+------------+------------------+-------------------+----------------------------------------------------------------------------+-------------tenk1
   | unique1 |         0 |         4 |         -1 |                  |                   |
{"1","966","1939","2937","3955","4994","5957","6934","8002","9044","9996"}|   0.0256536
 
(1 row)

(See the current include/catalog/pg_statistic.h file for more info about
what the columns mean.)

Comments?  Any suggestions on column names, layout, anything?  None of
this is set in stone as far as I'm concerned, it's just a first cut.
        regards, tom lane