Bug 1570988

#Cause:
PGwatch2 ( an external monitoring tool )  uses some pg extensions.
It seems that ovirt-engine-setup stops when it checks for functions not owned by 'engine' user.[*]

#Steps to resolve
Roy was able to resolve the issue by:
1) Removing pg_stat extension and relevant functions and extensions. 
        via # drop extension pg_stat_statements; pgstattuple; and uuid-ossp, then re-creating extension uuid-ossp
2) Using pg_restore on db dump from previous upgrade.
3) Run engine-setup for upgrade 
 
#Status:
- Upgrade successful.

(In reply to Yaniv Kaul from comment #1)
> #Cause:
> PGwatch2 ( an external monitoring tool )  uses some pg extensions.
> It seems that ovirt-engine-setup stops when it checks for functions not
> owned by 'engine' user.[*]

This is not the reason, the real reason is that the extension is installing its functions with the "engine" user on the "public" schema , now , in the upgrade process we drop and re-create all functions in the public schema that are owned by "engine" but an attempt to drop the extension functions will fail since the functions are depending on the extension and the engine user can not drop the extension since only db admin role is allowed to do so.

> 
> #Steps to resolve
> Roy was able to resolve the issue by:
> 1) Removing pg_stat extension and relevant functions and extensions. 
>         via # drop extension pg_stat_statements; pgstattuple; and uuid-ossp,
> then re-creating extension uuid-ossp

This does not resolve the problem, this is a bypass, you will still have the same issue on any extension that is installed into the engine database.

I had pushed a draft [1]  that solves this issue by skipping drop of extension functions when the engine database is upgraded, I think that this is the right approach to solve the problem and still allow any other PG extension to be installed on the database 

Please consider re-opening this BZ and test my patch as the solution to this BZ.


[1] https://gerrit.ovirt.org/#/c/90614/

> 2) Using pg_restore on db dump from previous upgrade.
> 3) Run engine-setup for upgrade 
>  
> #Status:
> - Upgrade successful.

Reopening and adding details: during the upgrade we are trying to remove all functions from public schema except uuid_ossp extension. But we should modify this code to not remove functions from any PostgreSQL extensions.

I installed pg_stat_statements, plpythonu and pgstattuple extensions to engine database.
By the upgrade, output of generate_drop_all_functions_syntax doesn't contain functions from extensions. So that seems alright. But it tries to drop view
from generate_drop_all_views_syntax:

DROP VIEW if exists pg_stat_statements CASCADE;

ERROR:  cannot drop view pg_stat_statements because extension pg_stat_statements requires it
HINT:  You can drop extension pg_stat_statements instead.


tested in ovirt-engine-setup-4.2.4.1-0.1.el7.noarch

Created attachment 1470241 [details]
engine setup log

I've tried to verify as follows:

 1) install ovirt-engine-4.2.5.1-0.1.el7ev.noarch 
 2) run engine-setup
 3) On engine db run:
      CREATE EXTENSION pg_stat_statements;
      CREATE EXTENSION pgstattuple;
      CREATE EXTENSION plpythonu;
 4) run engine-setup again

I got:

2018-07-24 10:52:51,591+0200 DEBUG otopi.ovirt_engine_setup.engine_common.database database.execute:242 Database: 'None', Statement: '
                select
                    nsp.nspname as object_schema,
                    cls.relname as object_name,
                    rol.rolname as owner,
                    case cls.relkind
                        when 'r' then 'TABLE'
                        when 'i' then 'INDEX'
                        when 'S' then 'SEQUENCE'
                        when 'v' then 'VIEW'
                        when 'c' then 'TYPE'
                    else
                        cls.relkind::text
                    end as object_type
                from
                    pg_class cls join
                    pg_roles rol on rol.oid = cls.relowner join
                    pg_namespace nsp on nsp.oid = cls.relnamespace
                where
                    nsp.nspname not in ('information_schema', 'pg_catalog') and
                    nsp.nspname not like 'pg_%%' and
                    rol.rolname != %(user)s
                order by
                    nsp.nspname,
                    cls.relname
            ', args: {'user': 'engine'}
2018-07-24 10:52:51,591+0200 DEBUG otopi.ovirt_engine_setup.engine_common.database database.execute:247 Creating own connection
2018-07-24 10:52:51,605+0200 DEBUG otopi.ovirt_engine_setup.engine_common.database database.execute:292 Result: [{'owner': 'postgres', 'object_name': 'pg_stat_statements', 'object_type': 'VIEW', 'object_schema':
 'public'}]
2018-07-24 10:52:51,606+0200 DEBUG otopi.context context._executeMethod:143 method exception
Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/otopi/context.py", line 133, in _executeMethod
    method['method']()
  File "/usr/share/ovirt-engine/setup/bin/../plugins/ovirt-engine-setup/ovirt-engine/db/schema.py", line 341, in _validation
    self._checkDatabaseOwnership()
  File "/usr/share/ovirt-engine/setup/bin/../plugins/ovirt-engine-setup/ovirt-engine/db/schema.py", line 245, in _checkDatabaseOwnership
    'Cannot upgrade the Engine database schema due to wrong '
RuntimeError: Cannot upgrade the Engine database schema due to wrong ownership of some database entities.

please provide the result of the following query :


select
                    nsp.nspname as object_schema,
                    cls.relname as object_name,
                    rol.rolname as owner,
                    case cls.relkind
                        when 'r' then 'TABLE'
                        when 'i' then 'INDEX'
                        when 'S' then 'SEQUENCE'
                        when 'v' then 'VIEW'
                        when 'c' then 'TYPE'
                    else
                        cls.relkind::text
                    end as object_type
                from
                    pg_class cls join
                    pg_roles rol on rol.oid = cls.relowner join
                    pg_namespace nsp on nsp.oid = cls.relnamespace
                where
                    nsp.nspname not in ('information_schema', 'pg_catalog') and
                    nsp.nspname not like 'pg_%%' and
                    rol.rolname != 'engine'
                order by
                    nsp.nspname,
                    cls.relname;

engine=# select
    nsp.nspname as object_schema,
    cls.relname as object_name,
    rol.rolname as owner,
    case cls.relkind
when 'r' then 'TABLE'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'TYPE'
    else
cls.relkind::text
    end as object_type
from
    pg_class cls join
    pg_roles rol on rol.oid = cls.relowner join
    pg_namespace nsp on nsp.oid = cls.relnamespace
where
    nsp.nspname not in ('information_schema', 'pg_catalog') and
    nsp.nspname not like 'pg_%%' and
    rol.rolname != 'engine'
order by
    nsp.nspname,
    cls.relname;
 object_schema |    object_name     |  owner   | object_type 
---------------+--------------------+----------+-------------
 public        | pg_stat_statements | postgres | VIEW
(1 row)

engine-setup ran successfully with pg_stat_statements, plpythonu and pgstattuple extensions. No pg_* tables or views are dropped.

verified in ovirt-engine-4.2.6.1-0.0.master.20180808134452.git7cf7f6b.el7.noarch