Wrong execution order of CREATE EXTENSION

[PaoloConte]

SQLDelight Version

2.0.2

SQLDelight Dialect

postgresql

Describe the Bug

When defining the schema with CREATE EXTENSION and CREATE TABLE statements I would expect them to be executed in the same order, but the compiled code puts CREATE EXENSION at the bottom of the schema creation:

schema.sq

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE IF NOT EXISTS users (
    id                    BIGSERIAL NOT NULL PRIMARY KEY,
    email                 CITEXT UNIQUE NOT NULL,
    password              VARCHAR(128) NOT NULL,
    salt                  VARCHAR(128) NOT NULL,
    created_at            TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login            TIMESTAMP WITH TIME ZONE,
    language              VARCHAR(8) NOT NULL DEFAULT ''
);

DatabaseImpl.kt

 override fun create(driver: SqlDriver): QueryResult.Value<Unit> {
      driver.execute(null, """
          |CREATE TABLE IF NOT EXISTS users (
          |    id                    BIGSERIAL NOT NULL PRIMARY KEY,
          |    email                 CITEXT UNIQUE NOT NULL,
          |    password              VARCHAR(128) NOT NULL,
          |    salt                  VARCHAR(128) NOT NULL,
          |    created_at            TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
          |    last_login            TIMESTAMP WITH TIME ZONE,
          |    language              VARCHAR(8) NOT NULL DEFAULT ''
          |)
          """.trimMargin(), 0)
      driver.execute(null, "CREATE EXTENSION IF NOT EXISTS citext", 0)
      driver.execute(null, "CREATE EXTENSION IF NOT EXISTS btree_gin", 0)
      driver.execute(null, "CREATE EXTENSION IF NOT EXISTS pg_trgm", 0)
      return QueryResult.Unit
    }

This doesn't work because the table itself is using citext.

Stacktrace

No response

[griffio]

🦺 This is an issue related to https://github.com/cashapp/sqldelight/issues/4474#issuecomment-1658348445

➰ The work around is to use migration files instead -so the CREATE EXTENSION could be run in a separate migration, before the CREATE TABLE.

Same problem with supporting CREATE TYPE https://github.com/AlecKazakova/sql-psi/issues/533. The topological sort for create statements is being done in sql-psi here: https://github.com/cashapp/sqldelight/blob/68692f23f1e6cb713413f66367a15a7441ea7ff1/sqldelight-compiler/src/main/kotlin/app/cash/sqldelight/core/lang/util/TreeUtil.kt#L225