Unexpected behavior with the Schema - partitioned tables

[tavaresgerson]

I have a table that is partitioned (+81 billion rows), but the schema registers all partitions as if they were independent tables, see:

Original schema:

export class ShopOrderSchema extends BaseModel {
  static $columns = [...] as const
  $columns = ShopOrderSchema.$columns
  @column()
  ...

What I didn't expect:

export class ShopOrders0Schema extends BaseModel { ... }

export class ShopOrders1Schema extends BaseModel { ... }

export class ShopOrders2Schema extends BaseModel { ... }

Is this correct?

[thetutlage]

Not the expected output. But can you share a solution on how to detect the partitions and ignore/filter them out?

[tavaresgerson]

My stack is Postgres, so to filter child partitions use (relispartition = true) when listing tables for schema generation.

In the file node_modules/@adonisjs/lucid/build/src/dialects/pg.js and on line 45 there is this:

   #compileGetAllTables(schemas) {
        return this.client
            .query()
            .from('pg_catalog.pg_tables')
            .select(['tablename as name', 'schemaname as schema'])
            .whereNotIn('schemaname', ['pg_catalog', 'information_schema'])
            .whereIn('schemaname', schemas)
            .orderBy('tablename', 'asc');
    }

And you can replace it with this which will work:

 #compileGetAllTables(schemas) {
      return this.client
        .query()
        .from('pg_catalog.pg_class as c')
        .join('pg_catalog.pg_namespace as n', 'n.oid', 'c.relnamespace')
        .select(['c.relname as name', 'n.nspname as schema'])
        .whereIn('c.relkind', ['r', 'p']) // common table + partitioned table (root)
        .where('c.relispartition', false) // excludes child partitions
        .whereNotIn('n.nspname', ['pg_catalog', 'information_schema'])
        .whereIn('n.nspname', schemas)
        .orderBy('c.relname', 'asc')

A suggested modification to avoid breaking the behavior is to add an option in schemaGeneration:

• includePartitionChildren?: boolean (default false for schema generation, or true if you want to avoid breaking the change)
• apply only in PgDialect#getAllTablesWithSchema.

Thank you for the excellent work. ☕