Postgres Pro
Downloads
Home   >   mailing lists

Thread: 8.3-beta4, analyze and db owner

8.3-beta4, analyze and db owner

From
Clodoaldo
Date:
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)

Regards, Clodoaldo Pinto Neto

Re: 8.3-beta4, analyze and db owner

From
Magnus Hagander
Date:
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

Re: 8.3-beta4, analyze and db owner

From
Clodoaldo
Date:
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

Re: 8.3-beta4, analyze and db owner

From
Alvaro Herrera
Date:
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