Date: Fri, 14 Oct 2005 11:53:13 -0400
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: owner of data type "areas" appears to be invalid ?
Message-Id: <20051014115313.7a05858a.gry@ll.mit.edu>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

[PostgreSQL 7.4RC2, x86 linux]
Several tables are giving the errors like:
  pg_dump: WARNING: owner of data type "areas" appears to be invalid
from pg_dump.

This is my production database, (50 users, 18 hours/day, 21MB compressed dump).

The output of "pg_dump -t areas" starts with:

REVOKE ALL ON TABLE areas FROM PUBLIC;
REVOKE ALL ON TABLE areas FROM geoyou;
SET SESSION AUTHORIZATION "101";
GRANT ALL ON TABLE areas TO "101" WITH GRANT OPTION;
RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION "101";
GRANT ALL ON TABLE areas TO PUBLIC;
RESET SESSION AUTHORIZATION;
GRANT ALL ON TABLE areas TO PUBLIC;

But "101" is the numerical group id of the "operator" group!  (see dump from pg_group below).  The table owner is "geoyou" as seen from the pg_class select below.  User "geoyou" is in pg_user with usesysid=501.  There is no row in pg_user with usesysid=101, and there is none with usename "101".

How can I fix this?  I must be able to get clean dumps that can be reloaded in case of a crash.

-- George Young

pig5=> select * from pg_class where relname='areas';
 relname | relnamespace | reltype  | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass |                relacl                 
---------+--------------+----------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+---------------------------------------
 areas   |         2200 | 14745246 |      501 |     0 |    14745245 |        1 |        41 |      14745247 |             0 | f           | f           | r       |        2 |         0 |           0 |        0 |        0 |       0 | t          | f          | f           | f              | {101=a*r*w*d*R*x*t*/101,=arwdRxt/101}

pig5=> select * from pg_user where usesysid=501;
 usename | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig 
---------+----------+-------------+----------+-----------+----------+----------+-----------
 geoyou  |      501 | f           | f        | f         | ******** |          | 

select * from pg_group where grosysid=101;
 groname  | grosysid |                                                                                                                                                                                                                                                                                                    grolist                                                                                                                                                                                                                                                                                                    
----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 operator |      101 | {602,616,509,525,614,514,617,539,517,558,581,567,506,609,94,511,573,17115,327,17422,512,537,626,503,519,583,547,570,584,78,10980,518,557,564,528,546,592,599,613,510,513,536,554,500,530,594,608,524,17114,533,17116,17289,17290,17292,17294,17345,17347,17421,17423,17425,214,17430,17427,17428,574,11,391,17431,17667,17703,8309,17769,17842,17773,17874,17877,13283,12758,17966,18888,17902,18099,18117,18129,18170,18173,18163,32766,18195,18202,18208,17786,17704,18375,18322,18399,18410,17904,18438,18424,28424,18437,102137,9877,502,32768,18553,13065,10681,8245,17049,15885,15886,8977,18706,18717}

select * from pg_tables where tablename='areas';
 schemaname | tablename | tableowner | hasindexes | hasrules | hastriggers 
------------+-----------+------------+------------+----------+-------------
 public     | areas     | geoyou     | f          | f        | f


-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)