8.3-beta4, analyze and db owner

Clodoaldo wrote:

I'm the owner of a database and when i issue an analyze command on it,
the pg tables are skipped with the message that only the owner can
analyze them:

$ psql fahstats -U cpn
Welcome to psql 8.3beta4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

fahstats=> analyze;
WARNING: skipping "pg_authid" --- only table or database owner can analyze it
WARNING: skipping "pg_database" --- only table or database owner can analyze it
WARNING: skipping "pg_tablespace" --- only table or database owner
can analyze it
WARNING: skipping "pg_pltemplate" --- only table or database owner
can analyze it
WARNING: skipping "pg_shdepend" --- only table or database owner can analyze it
WARNING: skipping "pg_shdescription" --- only table or database owner
can analyze it
WARNING: skipping "pg_auth_members" --- only table or database owner
can analyze it
ANALYZE
fahstats=> \l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
fahstats | cpn | SQL_ASCII
postgres | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(4 rows)

These are the global shared tables, that exist only once in the cluster
- not once per database. They can only be analyzed by "postgres" in this
case.

(There's a whole bunch of other pg_ tables that are local to the
database, and those should be analyzed just fine in this case)

Seems a bit unnecessary that we should even try and then emit a warning
for them though.

//Magnus

2008/1/6, Magnus Hagander <magnus@hagander.net>:

Clodoaldo wrote:

I'm the owner of a database and when i issue an analyze command on it,
the pg tables are skipped with the message that only the owner can
analyze them:

$ psql fahstats -U cpn
Welcome to psql 8.3beta4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

fahstats=> analyze;
WARNING: skipping "pg_authid" --- only table or database owner can analyze it
WARNING: skipping "pg_database" --- only table or database owner can analyze it
WARNING: skipping "pg_tablespace" --- only table or database owner
can analyze it
WARNING: skipping "pg_pltemplate" --- only table or database owner
can analyze it
WARNING: skipping "pg_shdepend" --- only table or database owner can analyze it
WARNING: skipping "pg_shdescription" --- only table or database owner
can analyze it
WARNING: skipping "pg_auth_members" --- only table or database owner
can analyze it
ANALYZE
fahstats=> \l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
fahstats | cpn | SQL_ASCII
postgres | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(4 rows)

These are the global shared tables, that exist only once in the cluster
- not once per database. They can only be analyzed by "postgres" in this
case.

(There's a whole bunch of other pg_ tables that are local to the
database, and those should be analyzed just fine in this case)

Seems a bit unnecessary that we should even try and then emit a warning
for them though.

The problem with that warning message is that it implies that the db
owner can analyze them which can not be done according to your
comment. The message, if emited, should say then that only superuser
can analyze those tables.

Regards, Clodoaldo Pinto Neto

Clodoaldo escribi�:

fahstats=> analyze;
WARNING: skipping "pg_authid" --- only table or database owner can analyze it

The problem with that warning message is that it implies that the db
owner can analyze them which can not be done according to your
comment. The message, if emited, should say then that only superuser
can analyze those tables.

How about this patch.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support