Table with Function dependency causes error from function being created after table.

[SirensOfTitan]

Consider the following simple example schema:

CREATE OR REPLACE FUNCTION best_number(OUT RESULT bigint)
  RETURNS bigint
  LANGUAGE plpgsql
AS $FUNCTION$
  DECLARE
  best_number bigint := 456;
BEGIN
  RESULT := best_number;
END;
$FUNCTION$;

CREATE TABLE test (
  best_id bigint NOT NULL DEFAULT best_number(),
  NAME VARCHAR(200) DEFAULT ''
);

If I use migra to sync a full-db from nothing, it attempts to execute the following statements:

create table "public"."test" (
    "best_id" bigint not null default best_number(),
    "name" character varying(200) default ''::character varying
);


set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.best_number(OUT result bigint)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
  DECLARE
  best_number bigint := 456;
BEGIN
  RESULT := best_number;
END;
$function$
;

... which leads to the following error, as FUNCTION comes after the create table statement: ERROR: function best_number() does not exist.

Would it be reasonable to suggest FUNCTION should come before the table creation statements? Or is more strict dependency tracking the answer here?

[djrobstep]

Thanks for filing this, this is a significant problem.

We probably do need to move the function creation before the table creation, and then check we haven't caused any other dependency issues in doing so. I'll investigate this when I can.

[djrobstep]

This should be fixed now - function creation has been moved to before table creation, unless the function has a table return type

[rhomel]

I'm still seeing this problem in the latest commit by the way. Was it actually fixed?

[rhomel]

Re-ordering the statements like this seems to work for our use-case: https://github.com/rhomel/migra/pull/1/commits/c4c84832904693d139b085d74d33c56ddad1d815

This is for Postgres 12 with the example SQL posted in the original issue.

[djrobstep]

Unfortunately moving non_table_selectable_creations breaks things because views etc can depend on newly created tables.

Even just moving functions before table creations because some functions have a table return type.

I believe a solution might be moving all function creates ahead of table creates unless they have a tabular return type, in which case they can go after.

When I have some time I'll try and make this change, and see how it handles the scenarios in the test suite.

[rhomel]

Thanks for looking into it.

I believe a solution might be moving all function creates ahead of table creates unless they have a tabular return type, in which case they can go after.

That would work for our case.

After spending some more time with the code, I think the best way might be to build a dependency graph to determine a good ordering. But I think cycles between objects are possible so this might also be difficult to solve for all cases.

[djrobstep]

Yes a dependency graph is the rigorous way to do it.

That would at least allow an informative error message if unsolvable dependencies were present. I've been meaning to implement this for a while, and will when time allows - although we seem to be most of the way there with this reasonably static order, particularly once we resolve this current issue.

[zineanteoh]

were there any workarounds for the "function ... does not exist (SQLSTATE 42883)" error?