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
