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

On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane 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