Thread: owner of data type "areas" appears to be invalid ?

owner of data type "areas" appears to be invalid ?

From
george young
Date:
[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_userwith 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)


Re: owner of data type "areas" appears to be invalid ?

From
Tom Lane
Date:
george young <gry@ll.mit.edu> writes:
> How can I fix this?

Re-create the owning user (which you evidently dropped), assigning it
sysid 101.

PG 8.1 will make it impossible to drop users who still own objects or
have permissions ... although that will bring its own set of gotchas ...
        regards, tom lane


Re: owner of data type "areas" appears to be invalid ?

From
george young
Date:
Yes, that worked.  Thank you very much!

-- George

On Fri, 14 Oct 2005 12:04:13 -0400
Tom Lane <tgl@sss.pgh.pa.us> threw this fish to the penguins:

> george young <gry@ll.mit.edu> writes:
> > How can I fix this?
> 
> Re-create the owning user (which you evidently dropped), assigning it
> sysid 101.
> 
> PG 8.1 will make it impossible to drop users who still own objects or
> have permissions ... although that will bring its own set of gotchas ...
> 
>             regards, tom lane
> 


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