Generated topological sort is incorrect at a macro transaction level

[paupino]

I have replicated with a very basic example:

CREATE TABLE public.allocation (
    id              serial                NOT NULL,
    CONSTRAINT pk_public_allocation PRIMARY KEY (id)
);
CREATE TABLE public.transaction (
    id                serial                NOT NULL,
    allocation_id     int                   NOT NULL,
    CONSTRAINT pk_public_transaction PRIMARY KEY (id),
    CONSTRAINT fk_public_transaction__allocation_id FOREIGN KEY (allocation_id)
      REFERENCES public.allocation (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

This gives the error: relation "public.allocation" does not exist.

[paupino]

Reproducing test: https://github.com/paupino/psqlpack/tree/bug/dependency_graph_generation

[paupino]

This is only really an issue due to the way that tables are created all at once. Ideally a table could be created all at once without concern, however worst case scenario is that we fall back to a micro transactional approach.

[paupino]

The graph generated for the above example is this:

Technically, the ordering is correct if you look at the individual components (i.e. fk_public_transaction__allocation_id relies on public.allocation implicitly via id).

[paupino]

I suspect this happens due to natural ordering of table names via g.node_identifiers(). It builds up the DFS starting from allocation therefore when it reverses the order it places transaction first.

Ordering is currently:

Table:      public.transaction
Column:     public.transaction.id
Column:     public.transaction.allocation_id
Constraint: public.transaction.pk_public_transaction
Table:      public.allocation
Column:     public.allocation.id
Constraint: public.allocation.pk_public_allocation
Constraint: public.transaction.fk_public_transaction__allocation_id