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.stadistinct AS 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, CASE WHEN (1 = s.stakind1) THEN s.stanumbers1 WHEN (1 = s.stakind2) THEN s.stanumbers2 WHEN (1 = s.stakind3) THEN s.stanumbers3 WHEN (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

Thu, 14 Jun 2001 18:09:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
pg_stats view added to CVS tip
Tom Lane writes:

> 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.)

For informational views this is okay AFAICS; the SQL information schema is
full of this kind of thing.  When you want to present alternative
information you add a new column and return null in the old ones.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sat, 16 Jun 2001 17:39:37 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: pg_stats view added to CVS tip