Introspection can't get composite primary key for partitioned table

[misha-erm]

Bug description

Hello 👋🏻
I'm trying to add Prisma to existing project and it fails on introspection step.
We have partitioned table with composite keys and prisma can't understand what primary key is in the original table. The fun fact is that it successfully introspected the same primary key for partitions themselves.

*** WARNING ***

The following models were commented out as they do not have a valid unique identifier or id. This is currently not supported by the Prisma Client.
- "blocks"
- "comments"
- "reactions"

Blocks, comments and reactions are tables with partitions

How to reproduce

1. Create a partitioned table with composite id. For example here is sql to create our blocks table:

-- Table: public.blocks

CREATE TABLE IF NOT EXISTS public.blocks
(
    id uuid NOT NULL,
    account text COLLATE pg_catalog."default" NOT NULL,
    holder_type_id uuid NOT NULL,
    holder_entity_id uuid NOT NULL,
    block_source_id uuid,
    type block_type NOT NULL,
    kind block_kind NOT NULL,
    created_by uuid NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    modified_at timestamp without time zone NOT NULL DEFAULT now(),
    rank integer NOT NULL,
    link_comment text COLLATE pg_catalog."default",
    content jsonb NOT NULL,
    is_deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT blocks_pkey PRIMARY KEY (account, id),
    CONSTRAINT block_source_block_fk FOREIGN KEY (block_source_id, account)
        REFERENCES public.blocks (id, account) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT blocks_account_block_source_id_fkey FOREIGN KEY (block_source_id, account)
        REFERENCES public.blocks_p2_0 (id, account) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT blocks_account_block_source_id_fkey1 FOREIGN KEY (block_source_id, account)
        REFERENCES public.blocks_p2_1 (id, account) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
) PARTITION BY HASH (account);

ALTER TABLE IF EXISTS public.blocks
    OWNER to postgres;

-- Index: entity_blocks_idx

CREATE INDEX IF NOT EXISTS entity_blocks_idx
    ON public.blocks USING btree
    (account COLLATE pg_catalog."default" ASC NULLS LAST, holder_type_id ASC NULLS LAST, holder_entity_id ASC NULLS LAST)
;

-- Partitions SQL

CREATE TABLE public.blocks_p2_0 PARTITION OF public.blocks
    FOR VALUES WITH (modulus 2, remainder 0);

ALTER TABLE IF EXISTS public.blocks_p2_0
    OWNER to postgres;
CREATE TABLE public.blocks_p2_1 PARTITION OF public.blocks
    FOR VALUES WITH (modulus 2, remainder 1);

ALTER TABLE IF EXISTS public.blocks_p2_1
    OWNER to postgres;

2. run npx prisma db pull

Expected behavior

Primary key should be introspected correctly for partitioned table. Now it prevents me from using Prisma completely since I can't generate a client

Prisma information

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

/// The underlying table does not contain a valid unique identifier and can therefore currently not be handled by the Prisma Client.
model blocks {
  id                                                      String           @db.Uuid
  account                                                 String
  holder_type_id                                          String           @db.Uuid
  holder_entity_id                                        String           @db.Uuid
  block_source_id                                         String?          @db.Uuid
  type                                                    block_type
  kind                                                    block_kind
  created_by                                              String           @db.Uuid
  created_at                                              DateTime         @default(now()) @db.Timestamp(6)
  modified_at                                             DateTime         @default(now()) @db.Timestamp(6)
  rank                                                    Int
  link_comment                                            String?
  content                                                 Json
  is_deleted                                              Boolean          @default(false)
  blocks                                                  blocks?          @relation("blocksToblocks_account_block_source_id", fields: [account, block_source_id], references: [account, id], onDelete: Cascade, onUpdate: NoAction, map: "block_source_block_fk")
  blocks_p2_0_blocks_account_block_source_idToblocks_p2_0 blocks_p2_0?     @relation("blocks_account_block_source_idToblocks_p2_0", fields: [account, block_source_id], references: [account, id], onDelete: Cascade, onUpdate: NoAction)
  blocks_p2_1_blocks_account_block_source_idToblocks_p2_1 blocks_p2_1?     @relation("blocks_account_block_source_idToblocks_p2_1", fields: [account, block_source_id], references: [account, id], onDelete: Cascade, onUpdate: NoAction, map: "blocks_account_block_source_id_fkey1")
  other_blocks                                            blocks[]         @relation("blocksToblocks_account_block_source_id")
  blocks_p2_0_blocksToblocks_p2_0_account_block_source_id blocks_p2_0[]    @relation("blocksToblocks_p2_0_account_block_source_id")
  blocks_p2_1_blocksToblocks_p2_1_account_block_source_id blocks_p2_1[]    @relation("blocksToblocks_p2_1_account_block_source_id")

  @@ignore
}

model blocks_p2_0 {
  id                                                 String      @db.Uuid
  account                                            String
  holder_type_id                                     String      @db.Uuid
  holder_entity_id                                   String      @db.Uuid
  block_source_id                                    String?     @db.Uuid
  type                                               block_type
  kind                                               block_kind
  created_by                                         String      @db.Uuid
  created_at                                         DateTime    @default(now()) @db.Timestamp(6)
  modified_at                                        DateTime    @default(now()) @db.Timestamp(6)
  rank                                               Int
  link_comment                                       String?
  content                                            Json
  is_deleted                                         Boolean     @default(false)
  blocks_blocksToblocks_p2_0_account_block_source_id blocks?     @relation("blocksToblocks_p2_0_account_block_source_id", fields: [account, block_source_id], references: [account, id], onDelete: Cascade, onUpdate: NoAction, map: "block_source_block_fk") @ignore
  blocks_blocks_account_block_source_idToblocks_p2_0 blocks[]    @relation("blocks_account_block_source_idToblocks_p2_0") @ignore

  @@id([account, id])
  @@index([account, holder_type_id, holder_entity_id])
}

model blocks_p2_1 {
  id                                                 String      @db.Uuid
  account                                            String
  holder_type_id                                     String      @db.Uuid
  holder_entity_id                                   String      @db.Uuid
  block_source_id                                    String?     @db.Uuid
  type                                               block_type
  kind                                               block_kind
  created_by                                         String      @db.Uuid
  created_at                                         DateTime    @default(now()) @db.Timestamp(6)
  modified_at                                        DateTime    @default(now()) @db.Timestamp(6)
  rank                                               Int
  link_comment                                       String?
  content                                            Json
  is_deleted                                         Boolean     @default(false)
  blocks_blocksToblocks_p2_1_account_block_source_id blocks?     @relation("blocksToblocks_p2_1_account_block_source_id", fields: [account, block_source_id], references: [account, id], onDelete: Cascade, onUpdate: NoAction, map: "block_source_block_fk") @ignore
  blocks_blocks_account_block_source_idToblocks_p2_1 blocks[]    @relation("blocks_account_block_source_idToblocks_p2_1") @ignore
  comments                                           comments[]  @ignore
  reactions                                          reactions[] @ignore

  @@id([account, id])
  @@index([account, holder_type_id, holder_entity_id])
}

enum block_kind {
  field
  native
  link
  transclusion
}

enum block_type {
  file
  view
  rich_text @map("rich-text")
}

Environment & setup

• OS: MacOs Monterey 12.0.1
• Database: PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
• Node.js version: v16.13.1

Prisma Version

prisma                  : 3.7.0
@prisma/client          : Not found
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 8746e055198f517658c08a0c426c7eec87f5a85f (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 8746e055198f517658c08a0c426c7eec87f5a85f (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 8746e055198f517658c08a0c426c7eec87f5a85f (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 8746e055198f517658c08a0c426c7eec87f5a85f (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 8746e055198f517658c08a0c426c7eec87f5a85f
Studio                  : 0.445.0

[janpio]

Here is a related feature request: #1708

We have partitioned table with composite keys and prisma can't understand what primary key is in the original table. The fun fact is that it successfully introspected the same primary key for partitions themselves.

Can you explain what this means please?

[misha-erm]

@janpio take a look at the generated schema.prisma:
As you can see models blocks_p2_0 and blocks_p2_1 have correct @id([account, id]) but the original model blocks doesn't have it.
From the attached SQL you can see that blocks_p2_0 and blocks_p2_1 are partitions of blocks table

[misha-erm]

I saw #1708 but it's mostly about how to describe partitions in schema.prisma. I was curious to see if Prisma is can be used with partitioned tables at least in introspection mode

[janpio]

No, not out of the box - that is exactly what issue #1708 is about.

The problem for what you are observing is probably that the database does not actually return a representation of the primary key on the blocks table.

You can possibly modify the blocks model to match what the actual table looks like, then remove the other 2 models for the partitions and it could potentially work as intended. Note that prisma migrate and prisma db push commands of course would not be able to recreate a database similar to the original one.

[misha-erm]

It could be a workaround but since I'd have to modify blocks model after each introspection looks like it's not an option for us. Do you know if there are some plans on supporting partitions?

Thank you

[janpio]

Sooner or later, yes - see #1708. But currently it is not very high on the list at the moment. (Doesn't mean it could not just happen in the next few months)

[jineshshah36]

We have adopted prisma in a very large postgres database that syncs integration data and want to partition some tables. I'd be very excited to have a way (even if suboptimal) to achieve partitioning. We already write many manual migrations that cannot be expressed in prisma as it is. I don't mind doing that for partitions, but the composite key issue is a blocker.

[janpio]

@MikeYermolayev It has been some while, but maybe you are still around and working with the same database:
When I try to reproduce your result, with a fixed and minimal SQL, I only get 2 models:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model blocks_p2_0 {
  id              String  @db.Uuid
  account         String
  block_source_id String? @db.Uuid

  @@id([account, id])
}

model blocks_p2_1 {
  id              String  @db.Uuid
  account         String
  block_source_id String? @db.Uuid

  @@id([account, id])
}

CREATE TABLE IF NOT EXISTS public.blocks
(
    id uuid NOT NULL,
    account text COLLATE pg_catalog."default" NOT NULL,
    block_source_id uuid,
    CONSTRAINT blocks_pkey PRIMARY KEY (account, id)
) PARTITION BY HASH (account);


CREATE TABLE public.blocks_p2_0 PARTITION OF public.blocks
    FOR VALUES WITH (modulus 2, remainder 0);

CREATE TABLE public.blocks_p2_1 PARTITION OF public.blocks
    FOR VALUES WITH (modulus 2, remainder 1);

ALTER TABLE public.blocks
      ADD CONSTRAINT block_source_block_fk FOREIGN KEY (block_source_id, account)
        REFERENCES public.blocks (id, account) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE    

So Prisma does not pick up the main table, but picks up only the 2 partitions.

Is it possible that Prisma changed in this regard?

[janpio]

@MrLoh I think your problem here is a bit different, so I split it into its own issue, so we can understand it better: #17348 Would be great if we could continue the conversation there.

[janpio]

Ok, I went back and tried again (as I should have initially) with the Prisma version 3.7.0 that @MikeYermolayev mentioned above, and I can now indeed reproduce the problem you are describing: Prisma is picking up the main table, but not its composite primary key.

Today in Prisam 4.8.1 it looks to me now after trying these both reproductions that Prisma in general can not pick up the main table when it is partitioned at all, but only picks up the partitions itself (and then sometimes does a bad job working with that, see #17348).

So technically this issue is not valid any more, and got worse in that Prisma does not pick up the main table any more at all.

[janpio]

This issue took what Prisma brokenly introspected (with missing composite primary key) for the table, and tried to fix it. But is this even how a partitioned table should be represented in Prisma schema? I asked in our feature request issue and would welcome your responses: #1708 (comment)

[misha-erm]

oh, I'm sorry but I don't longer have this database around and I haven't touched prisma for a while ((
I won't be able to assist you with this now(

[janpio]

Release 4.10.0 later today will improve Introspection of partitioned tables for PostgreSQL and MySQL. Only the main table will be represented in the Prisma Schema, but cleanly and in a way that Prisma can handle and that should not break any other CLI commands. We explicitly tested this with the SQL @MikeYermolayev provided - so this should be good now.

For full support of partitioned tables, please follow and subscribe to #1708