Functions reported as changed when querying with different users

[regularfellow]

Hello ❤️, It appears when checking the difference between databases with different users migra reports functions as changed even when it is the same. I could not figure out why this happens. It may have something to do with the users having different grants or one being a superuser. Other types of entities do not appear to be reported changed, only functions.

I made an example repo: https://github.com/regularfellow/migra-testing

$ psql -U postgres -h localhost -c "CREATE DATABASE testdb;"
$ psql -U postgres -h localhost -c "CREATE ROLE testuser WITH LOGIN PASSWORD 'testuser';"
$ psql -U postgres -h localhost -c "GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;"
$ psql -U postgres -h localhost -f schema.sql testdb
$ migra postgresql://postgres:postgres@localhost:5432/testdb postgresql://testuser:testuser@localhost:5432/testdb
set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.notify()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    PERFORM pg_notify('notify', 'message');
    RETURN NEW;
END;
$function$
;

[robrice]

I have seen a similar issue with Postgres version 13.3 comparing 13.7 trigger functions that are the same (from two different databases - but the same schema name) even when there are definately the same. The create statement in pgadmin (create script) does include a couple of parameters that are NOT included in the migra create script. for example, migra issues this re-create script:

Function create by migra:

CREATE OR REPLACE FUNCTION schema.sample() RETURNS trigger LANGUAGE plpgsql AS $function$^M DECLARE^M BEGIN^M UPDATE work_units.wu_connections SET geom = work_units.calculate_wu_connection_geom(wu_connections.id)^M WHERE wu_connections.id = NEW.id;^M RETURN NEW;^M END;^M $function$ ;

function as provided by the pg_admin (create script) for the same table/function after applying the above script. But every time I run migra it will give me the same function script (above):

-- FUNCTION: work_units.update_wu_connection_geom_for_wu_connection() -- DROP FUNCTION work_units.update_wu_connection_geom_for_wu_connection();

CREATE FUNCTION schema.sample() RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ DECLARE BEGIN UPDATE work_units.wu_connections SET geom = work_units.calculate_wu_connection_geom(wu_connections.id) WHERE wu_connections.id = NEW.id; RETURN NEW; END; $BODY$;

[Apakottur]

I just had a similar issue, not sure if it's the same one but might help others.

In my case the function definition diff was due to lower/upper case differences between the two DBs. For example one DB had:

select floor(1);

While the other had:

SELECT FLOOR(1);

These SQL statements are equivalent but Migra expects an exact match.

Modifying both functions to be in the exact same casing makes Migra show no diff in my case.