On Fri, Jul 6, 2018 at 10:01 AM, Moreno Andreo <moreno.and...@evolu-s.it>
wrote:
> Hi,
> Running 9.1 on Windows 10, upgrading to 10 with pg_upgrade.
>
> "Once upon a time" there was a bug in our automatic role creation
> procedure that did not mask vowels with accent (used in Italian language),
> like "ò, è" and the result was a role with an empty name.
> We are now upgrading to 10, and pg_dumpall exits complaining with this
> role, showing its name (with mis-encoded UTF-8 accented vowel) as an
> invalid utf-8 character.
>
> Trying to get rid of the role, that can't be deleted with a drop role
> because of the empty name, I did
> delete from pg_authid where oid = nnnn
>
> Role disappeared from role list.
>
> At the next execution of the pg_upgrade it complains that role "nnnn" does
> not exist while dumping a trigger function. I tried remove the privilege
> from function ACL, but "role nnnnn does not exists".
>
> Is there a way to recreate the deleted role, either as a dummy, so I can
> finish upgrade?
> Is there another way to bypass the problem?
>
> Any help would be appreciated.
>
> Cheers,
> Moreno.-
>
>
>
>Is there a way to recreate the deleted role, either as a dummy, so I can
finish upgrade?
I can't really suggest how to recreate the dummy role, but I do have an
alternate solution.
Most probably pg_dump is complaining that role 'xxx' owns some tables. So
you can use the
attached script and add 'AND a.rolname = 'xxx' to the WHERE clause.
Then as a superuser you can use ALTER TABLE xyz OWNER TO new_owner for each
table found.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
SELECT c.relname as table,
a.rolname as owner,
c.relacl as permits
FROM pg_class c
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind = 'r'
--AND position('cp' in ARRAY_TO_STRING(c.relacl, '') )> 0
ORDER BY relname;
