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