Re: pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

Again, as the list software doesn't like "config" at the
start of a line.

Karsten

On Fri, Jan 08, 2016 at 05:34:00PM +0100, Karsten Hilbert wrote:

> > > (For what it's worth, I have also tried the --method=dump way
> > > of using Debian's pg_upgradecluster which internally uses a
> > > dump/restore cycle rather than calling pg_upgrade. That
> > > failed due to ordering problems with table data vs table
> > > constraints.)
> >
> > That seems like an independent bug.  Can you provide specifics?
>
> Attached the log of
>
>     pg_upgradecluster -v 9.5 9.4 main &> pg-upgrade-9_4-9_5-dump_restore.log
>
> and here is the function that leads to the schema having a
> dependancy on table data:
>
>     create or replace function gm.account_is_dbowner_or_staff(_account name)
>         returns boolean
>         language plpgsql
>         as '
>     DECLARE
>         _is_owner boolean;
>     BEGIN
>         -- is _account member of current db group ?
>     --    PERFORM 1 FROM pg_auth_members
>     --    WHERE
>     --        roleid = (SELECT oid FROM pg_roles WHERE rolname = current_database())
>     --            AND
>     --        member = (SELECT oid FROM pg_roles WHERE rolname = _account)
>     --    ;
>     --    IF FOUND THEN
>     --        -- should catch people on staff, gm-dbo, and postgres
>     --        RETURN TRUE;
>     --    END IF;
>
>         -- postgres
>         IF _account = ''postgres'' THEN
>             RETURN TRUE;
>         END IF;
>
>         -- on staff list
>         PERFORM 1 FROM dem.staff WHERE db_user = _account;
>         IF FOUND THEN
>             RETURN TRUE;
>         END IF;
>
>         -- owner
>         SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO STRICT _is_owner FROM pg_catalog.pg_database WHERE
datname= current_database(); 
>         IF _is_owner IS TRUE THEN
>             RETURN TRUE;
>         END IF;
>
>         -- neither
>         RAISE EXCEPTION
>             ''gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff'',
_account
>             USING ERRCODE = ''integrity_constraint_violation''
>         ;
>         RETURN FALSE;
>     END;';
>
> The function is used on audit tables:
>
>     alter table audit.audit_fields
>         drop constraint if exists
>             audit_audit_fields_sane_modified_by cascade;
>
>     alter table audit.audit_fields
>         add constraint audit_audit_fields_sane_modified_by check
>             (gm.account_is_dbowner_or_staff(modified_by) IS TRUE)
>     ;
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346