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.