Keep create statements order if ORM schema export is used as desired schema (bugfix plan for pgledger)

[jozef-slezak]

I use drizzle-kit export as advised on https://www.pgschema.com/workflow/orm#work-with-orm

Drizzle kit properly ordres sql statements so I would prefere not to reorder create statements in pgschema. I would suggest new optional CLI flag to skip reordering. I am also using third party pgr0ss/pgledger but I can maintain sorted include\i.

I would still benefit from hashing and diff (even if the reordering is skipped). I believe that we can spare alot of possible bugs and even spare some compute time since the source is properly ordered (generated from ORM - not manually handcrafted).

Bug: I tried use pgschema v1.12.1 with https://github.com/pgr0ss/pgledger/blob/main/pgledger.sql and found bug in reordering. pgschema reorder bug doesn't recognize table implicit composite types in function signatures

I inspected both pgschema v1.12.1—the version in your plan.json—and current main. The answer is more specific than “functions before tables”:

In v1.12.1, the create order is essentially:

types without deferred deps
sequences

tables WITHOUT function/domain dependencies
↓
functions without view dependencies
↓
domains depending on functions
procedures
↓
tables WITH function/domain dependencies
↓
aggregates
views
functions depending on views

This is explicit in the source. Tables are divided into tablesWithoutDeps and tablesWithDeps; tables whose defaults/checks reference newly created functions go into the second bucket. Then pgschema creates the first table bucket, creates functions, and only afterward creates tablesWithDeps.

That explains exactly what happened to pgledger_accounts.

Your table contains:

CREATE TABLE pgledger_accounts (
id TEXT PRIMARY KEY DEFAULT pgledger_generate_id('pgla'),
...
);

pgledger_generate_id is also a newly created function. Therefore v1.12.1 sees:

pgledger_accounts
└── DEFAULT pgledger_generate_id(...)
↑
dependency

and classifies pgledger_accounts as tablesWithDeps. The source code explicitly checks table defaults/generated expressions/check constraints for references to new functions.

So pgschema intentionally moves this table to the second table batch:

tablesWithoutDeps
functions
tablesWithDeps

The bug is on the opposite dependency.

You also have:

pgledger_check_account_balance_constraints(
account PGLEDGER_ACCOUNTS
)

That means:

pgledger_check_account_balance_constraints
└── parameter type pgledger_accounts
↑
implicit row type
↑
pgledger_accounts table

So the real graph contains a cycle-like ordering requirement:

pgledger_generate_id
↓
pgledger_accounts
↓
pgledger_check_account_balance_constraints

Therefore the valid ordering is:

CREATE FUNCTION pgledger_generate_id(...);

CREATE TABLE pgledger_accounts (...);

CREATE FUNCTION pgledger_check_account_balance_constraints(
account pgledger_accounts
);

But v1.12.1 only knows one side of that relationship. It knows:

table → new function used by DEFAULT

so it delays the table. It does not check whether a function parameter/return type is the implicit composite type of a newly created table.

Consequently it produces:

pgledger_generate_id
pgledger_check_account_balance_constraints <-- too early
pgledger_accounts

which is exactly what your generated plan shows.

There is another interesting confirmation in the code: pgschema already implements this exact kind of dependency detection for views. functionReferencesNewView() explicitly examines both the function's return type and every parameter's DataType, specifically because views also expose composite types.

Conceptually it does:

// return type
typeName := extractBaseTypeName(fn.ReturnType)
if typeMatchesLookup(typeName, fn.Schema, newViews) {
return true
}

// parameter types
for _, param := range fn.Parameters {
typeName := extractBaseTypeName(param.DataType)
if typeMatchesLookup(typeName, fn.Schema, newViews) {
return true
}
}

But there is no equivalent functionReferencesNewTable() check for function signatures in v1.12.1.

Current main is interesting too. It recently added functionReferencesNewTable() for issue #530, and now separates:

functionsWithoutTableDeps
functionsWithTableDeps

so functions that query delayed tables can be emitted later.

However, I inspected that new function too. It only scans the function body for SQL patterns such as FROM, JOIN, INSERT INTO, UPDATE, and DELETE FROM.

Roughly:

func functionReferencesNewTable(fn *ir.Function, newTables map[string]struct{}) bool {
matches := tableRefPattern.FindAllStringSubmatch(fn.Definition, -1)
...
}

It does not inspect fn.Parameters or fn.ReturnType for table row types.

So your particular case appears to reveal a gap that likely still exists on current main.

The source already contains almost exactly the helper needed to fix it: functionReferencesNewView(). A robust solution would generalize that into something like:

func functionSignatureReferencesRelation(
fn *ir.Function,
relations map[string]struct{},
) bool

and use it for both views and tables.

For your case the dependency test needs to recognize:

param.DataType == "pgledger_accounts"

as a dependency on newly created table:

public.pgledger_accounts

and put that function in the post-table function batch.

So the diagnosis after inspecting the code is:

pgschema did not blindly reorder functions before tables. It correctly delayed pgledger_accounts because its default depends on pgledger_generate_id, but it failed to also delay pgledger_check_account_balance_constraints because it doesn't recognize table implicit composite types in function signatures.

Possible fix with functionSignatureReferencesRelation:

diff --git a/internal/diff/diff.go b/internal/diff/diff.go
--- a/internal/diff/diff.go
+++ b/internal/diff/diff.go
@@
 // functionReferencesNewView determines if a function references any newly added views
 // in its return type or parameter types. This handles cases where functions use
 // view composite types (e.g., RETURNS SETOF view_name or parameter of view_name type).
 func functionReferencesNewView(fn *ir.Function, newViews map[string]struct{}) bool {
-	if len(newViews) == 0 || fn == nil {
+	return functionSignatureReferencesRelation(fn, newViews)
+}
+
+// functionSignatureReferencesRelation determines whether a function's return type
+// or parameter types reference a relation composite type. PostgreSQL exposes both
+// tables and views as composite types, so a function using one in its signature
+// must be created after that relation exists.
+func functionSignatureReferencesRelation(fn *ir.Function, relations map[string]struct{}) bool {
+	if len(relations) == 0 || fn == nil {
 		return false
 	}
+
 	// Check return type (e.g., "SETOF public.actor", "actor", "SETOF actor")
 	if fn.ReturnType != "" {
 		typeName := extractBaseTypeName(fn.ReturnType)
-		if typeMatchesLookup(typeName, fn.Schema, newViews) {
+		if typeMatchesLookup(typeName, fn.Schema, relations) {
 			return true
 		}
 	}

@@
 	for _, param := range fn.Parameters {
 		if param.DataType != "" {
 			typeName := extractBaseTypeName(param.DataType)
-			if typeMatchesLookup(typeName, fn.Schema, newViews) {
+			if typeMatchesLookup(typeName, fn.Schema, relations) {
 				return true
 			}
 		}
@@
 // avoiding false positives from comments, literals, and aliases.
 // See https://github.com/pgplex/pgschema/issues/530
 func functionReferencesNewTable(fn *ir.Function, newTables map[string]struct{}) bool {
-	if len(newTables) == 0 || fn == nil || fn.Definition == "" {
+	if len(newTables) == 0 || fn == nil {
 		return false
 	}
+
+	// A table also defines a PostgreSQL composite row type. Functions that use a
+	// newly-added deferred table as a parameter or return type must therefore be
+	// created after the table, even if the function body never references it.
+	if functionSignatureReferencesRelation(fn, newTables) {
+		return true
+	}
+
+	if fn.Definition == "" {
+		return false
+	}
+
 	matches := tableRefPattern.FindAllStringSubmatch(fn.Definition, -1)
 	for _, match := range matches {
 		if len(match) < 2 {