Thread: owner of type 'mmm' apperars to be invalid

On Tuesday 05 Nov 2002 5:57 am, cristi wrote:
>  When I'm trying to make pg_dump on a data base I have this message:
>
> WARNING: owner of type 'mmm' apperars to be invalid
> WARNING: owner of type 'eee' apperars to be invalid
> WARNING: owner of type 'refcursor' apperars to be invalid
>
> Where are these object and how can I destroy its?

richardh=> select oid,typname,typowner from pg_type where typname='refcursor';oid  |  typname  | typowner
------+-----------+----------1790 | refcursor |        1

richardh=> select * from pg_user where usesysid=1;usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
passwd   
| valuntil
----------+----------+-------------+----------+----------+-----------+----------+----------postgres |        1 | t
    | t        | t        | t         |  

I'd recommend you add a user with the required id, then dump the database and
fix the dump as required. The owner of "refcursor" should be postgres (or
whatever the system user is called) - if that user has been deleted, I'd be
surprised that's the only errors you get.

--  Richard Huxton

It shouldn't be this the solution of the problem?

salt=# delete from pg_type where typname='refcursor';
DELETE 1
salt=# delete from pg_type where typname='eee';
DELETE 1
salt=# delete from pg_type where typname='mmm';
DELETE 1

Now, I don't have the error message!

Mr.Richard Huxton, thank you very much!

Cristi



On Tuesday 05 Nov 2002 11:55 am, cristi wrote:
> I'm in this situation:
>
> salt=# select oid,typname,typowner from pg_type where typname='refcursor';
>    oid    |  typname  | typowner
> ----------+-----------+----------
>  13701973 | refcursor |        0
> (1 row)
>
> salt=# select * from pg_user where usesysid=0;
>  usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd

> (0 rows)

Hmm - try the following (it should show the 5 users with lowest number - the
first should be 'postgres').

SELECT * FROM pg_user ORDER BY usesysid limit 5;

> 1)Which is the user with the required id in my case?
> 2)How can I  add a user with the required id?(the command)

Hmm - assuming the query above is good, I'd change "typowner" in "pg_type"
instead. Also the following should all display the same typowner (but we
know
there is a problem with refcursor). That typowner should be the postgres
user.

SELECT typname,typowner FROM pg_type WHERE typname LIKE 'r%';


-- Richard Huxton

On Tuesday 05 Nov 2002 1:03 pm, cristi wrote:
> It shouldn't be this the solution of the problem?
>
> salt=# delete from pg_type where typname='refcursor';
> DELETE 1
> salt=# delete from pg_type where typname='eee';
> DELETE 1
> salt=# delete from pg_type where typname='mmm';
> DELETE 1
>
> Now, I don't have the error message!
>
> Mr.Richard Huxton, thank you very much!

But - you have deleted the "refcursor" type which is not a good idea! If you
don't use cursors you might get away with it, but I'd be nervous about it.

If you create a new database does it contain the refcursor type? If so, drop
and recreate the database, restoring the data to it. If not, I think you'll
need to restore the template1 db - there's an article on
techdocs.postgresql.org about this (byJosh IIRC).
--  Richard Huxton

On Tuesday 05 Nov 2002 1:03 pm, cristi wrote:
> It shouldn't be this the solution of the problem?

>> salt=# delete from pg_type where typname='refcursor';
>> DELETE 1


>> Now, I don't have the error message!
> >Mr.Richard Huxton, thank you very much!

>But - you have deleted the "refcursor" type which is not a good idea! If
you
>don't use cursors you might get away with it, but I'd be nervous about it.

>If you create a new database does it contain the refcursor type?

Ooo!!!!
Yes, I see...
You right.

>If so, drop and recreate the database, restoring the data to it. If not, I
think you'll
>need to restore the template1 db - there's an article on
>techdocs.postgresql.org about this (byJosh IIRC).

Would you give me the URL address for this article?
Please!

On Tuesday 05 Nov 2002 2:32 pm, cristi wrote:
> On Tuesday 05 Nov 2002 1:03 pm, cristi wrote:
> >But - you have deleted the "refcursor" type which is not a good idea! If

> Ooo!!!!
> Yes, I see...
> You right.
>
> >If so, drop and recreate the database, restoring the data to it. If not, I
> think you'll
> >need to restore the template1 db - there's an article on
> >techdocs.postgresql.org about this (byJosh IIRC).
>
> Would you give me the URL address for this article?
> Please!

It's "Adventures in PostgreSQL, Episode 1 by Josh Berkus"
at http://techdocs.postgresql.org/
Loads of other good stuff there too.

Might not be necessary though - if the template1 db contains the refcursor
type definition then you just need to drop and recreate the problem database.

--  Richard Huxton