Re: type "xxxxxxx" does not exist

On 05/19/2017 01:06 PM, Micky Hulse wrote:
> Hello,
>
> I hope this is the right list for me to ask questions about psql.
> Please let me know if I am in the wrong place. :)
>
> I am far from an advanced user of PostgreSQL, so please bear with me ...
>
> I am working with an inherited database/codebase. I am trying to call
> this function via psql:
>
> # SELECT * FROM functionName('xxxxxxx', 'xxxxxxx', 'xxxxxxx');
>
> What I get back is this:
>
> ERROR: type "xxx_xxx_xxxxx" does not exist
> LINE 1: DECLARE results xxx_xxx_xxxxx;
> ^
> QUERY: DECLARE results xxx_xxx_xxxxx;
> .....
> .....
>
>
> When listing the functions, I see that functionName() does exist in
> the database.
>
> The type also exists (I think):
>
> # select exists (select 1 from pg_type where typname = 'xxx_xxx_xxxxx');
> exists
> --------
> t
> (1 row)
>
> Note that the role that owns the 'type' is not the same user that is
> calling the "functionName()" from the psql prompt. When I try to
> switch roles, using:
>
> sudo -i -u username

You should not need to do above.

> psql -U otherusername -d database

Just do the above.

Are either username or otherusername a superuser?

In psql \du will show you.

>
> … I get:
>
> psql: FATAL: Peer authentication failed for user "otherusername"

This is coming from:

https://www.postgresql.org/docs/9.6/static/auth-methods.html#AUTH-PEER

which is set in pg_hba.conf.

What version of Postgres, OS and how was it installed?

I am asking because that will help find where pg_hba.conf is. If you
have found it, can you share it here?

>
> Do I need to create a Linux user to login as "otherusername" so I can
> test calling the functionName() with xxx_xxx_xxxxx type?

No that is not necessary. Postgres usernames do not have to be the same
as the OS usernames. Peer authentication is just a method to map OS
usernames to Postgres usernames if you want to.

>
> Lastly, the type was declared in the SQL dump like this:
>
> CREATE TYPE xxx_xxx_xxxxx AS (
> ....
>
> );
> ALTER TYPE xxx_xxx_xxxxx OWNER TO otherusername;
>
>
> I know that's a lot of info ... More than anything, I'm just wondering
> if someone can give me tips on where to focus my attention in terms of
> trouble shooting?
>
> Thanks so much!
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com