Xata•4mo ago

Re-executing Drizzle Kit push fails after successful push

I'm getting the following error: PL/pgSQL function "xata.pg_catalog".regnamespace(text) line 14 at RAISE

> pnpx drizzle-kit push
drizzle-kit: v0.21.4
drizzle-orm: v0.30.10

No config path provided, using default path
Reading config file '/redacted/apps/partner-tool/app/drizzle.config.ts'
Using 'pg' driver for database querying
[⣟] Pulling schema from database.../redacted/node_modules/.pnpm/drizzle-kit@0.21.4_@esbuild-kit+esm-loader@2.6.5_commander@10.0.1_drizzle-orm@0.30.10_@neonda_jne73tzufwvw4dok65xkag4jqi/node_modules/drizzle-kit/bin.cjs:69429
        Error.captureStackTrace(err2);
              ^

error: schema "publics" does not exist
    at /redacted/node_modules/.pnpm/drizzle-kit@0.21.4_@esbuild-kit+esm-loader@2.6.5_commander@10.0.1_drizzle-orm@0.30.10_@neonda_jne73tzufwvw4dok65xkag4jqi/node_modules/drizzle-kit/bin.cjs:69429:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.query (/redacted/node_modules/.pnpm/drizzle-kit@0.21.4_@esbuild-kit+esm-loader@2.6.5_commander@10.0.1_drizzle-orm@0.30.10_@neonda_jne73tzufwvw4dok65xkag4jqi/node_modules/drizzle-kit/bin.cjs:111366:26)
    at async /redacted/node_modules/.pnpm/drizzle-kit@0.21.4_@esbuild-kit+esm-loader@2.6.5_commander@10.0.1_drizzle-orm@0.30.10_@neonda_jne73tzufwvw4dok65xkag4jqi/node_modules/drizzle-kit/bin.cjs:15979:46 {
  length: 166,
  severity: 'ERROR',
  code: 'P0001',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'PL/pgSQL function "xata.pg_catalog".regnamespace(text) line 14 at RAISE',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'pl_exec.c',
  line: '3923',
  routine: 'exec_stmt_raise'
}

> pnpx drizzle-kit push
drizzle-kit: v0.21.4
drizzle-orm: v0.30.10

No config path provided, using default path
Reading config file '/redacted/apps/partner-tool/app/drizzle.config.ts'
Using 'pg' driver for database querying
[⣟] Pulling schema from database.../redacted/node_modules/.pnpm/drizzle-kit@0.21.4_@esbuild-kit+esm-loader@2.6.5_commander@10.0.1_drizzle-orm@0.30.10_@neonda_jne73tzufwvw4dok65xkag4jqi/node_modules/drizzle-kit/bin.cjs:69429
        Error.captureStackTrace(err2);
              ^

error: schema "publics" does not exist
    at /redacted/node_modules/.pnpm/drizzle-kit@0.21.4_@esbuild-kit+esm-loader@2.6.5_commander@10.0.1_drizzle-orm@0.30.10_@neonda_jne73tzufwvw4dok65xkag4jqi/node_modules/drizzle-kit/bin.cjs:69429:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.query (/redacted/node_modules/.pnpm/drizzle-kit@0.21.4_@esbuild-kit+esm-loader@2.6.5_commander@10.0.1_drizzle-orm@0.30.10_@neonda_jne73tzufwvw4dok65xkag4jqi/node_modules/drizzle-kit/bin.cjs:111366:26)
    at async /redacted/node_modules/.pnpm/drizzle-kit@0.21.4_@esbuild-kit+esm-loader@2.6.5_commander@10.0.1_drizzle-orm@0.30.10_@neonda_jne73tzufwvw4dok65xkag4jqi/node_modules/drizzle-kit/bin.cjs:15979:46 {
  length: 166,
  severity: 'ERROR',
  code: 'P0001',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'PL/pgSQL function "xata.pg_catalog".regnamespace(text) line 14 at RAISE',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'pl_exec.c',
  line: '3923',
  routine: 'exec_stmt_raise'
}

Is this an error with Xata or Drizzle? It loks like Xata. Also, I'm not referencing publics anywhere. Which seems like a typo?

8 Replies

kostas•4mo ago

Hi, I believe this is a known issue and we're working on a fix. It should be available very soon.

Michael Schaufelberger•4mo ago

Ah, ok, thank you. In the meantime I've found that the issue is triggered by the use of a composite primary key:

    cpk: primaryKey({ columns: [table.guestId, table.guestTagId] }),

    cpk: primaryKey({ columns: [table.guestId, table.guestTagId] }),

After successfully pushing this. Drizzle cannot introspect the db again and it results in the above error.

kostas•4mo ago

Can you specify your workspace id please, so we can check for logs in more depth? Also a timestamp when this occurs would help us

Michael Schaufelberger•4mo ago

Sure. 5713tr is the id and the timestamp is basically for the last 20mins Do you need it more specific?

kostas•4mo ago

We've seen this issue before and the dev team confirmed that we've pushed a fix for it, but there may be more conditions that it manifests under. Would you be able to send us (here, or at support@xata.io if you don't want to post it in public) the drizzle schema you're using so we can reproduce?

Michael Schaufelberger•4mo ago

In this case it's fine, since it's pretty generic: This is what drizzle kit with config verbose: true produces

CREATE TABLE IF NOT EXISTS "guestsToGuestTags" (
        "id" text DEFAULT xata_private.xid() NOT NULL,
        "xata_id" text DEFAULT 'rec_'::text || (xata_private.xid())::text NOT NULL,
        "xata_version" integer DEFAULT 0 NOT NULL,
        "xata_createdat" timestamp with time zone DEFAULT now() NOT NULL,
        "xata_updatedat" timestamp with time zone,
        "guestId" text,
        "guestTagId" text,
        CONSTRAINT "guestsToGuestTags_guestId_guestTagId_pk" PRIMARY KEY("guestId","guestTagId")
);

CREATE TABLE IF NOT EXISTS "guestsToGuestTags" (
        "id" text DEFAULT xata_private.xid() NOT NULL,
        "xata_id" text DEFAULT 'rec_'::text || (xata_private.xid())::text NOT NULL,
        "xata_version" integer DEFAULT 0 NOT NULL,
        "xata_createdat" timestamp with time zone DEFAULT now() NOT NULL,
        "xata_updatedat" timestamp with time zone,
        "guestId" text,
        "guestTagId" text,
        CONSTRAINT "guestsToGuestTags_guestId_guestTagId_pk" PRIMARY KEY("guestId","guestTagId")
);

table.ts

export const guestsToGuestTags = pgTable(
  'guestsToGuestTags',
  {
    ...XATA_SCHEMA_FIELDS_AND_ID,
    guestId: text('guestId'),
    guestTagId: text('guestTagId'),
  },
  (table) => ({
    ...getXataSchemaIdIndex('guestsToGuestTags', table),
    cpk: primaryKey({ columns: [table.guestId, table.guestTagId] }),
  }),
);

export const guestsToGuestTags = pgTable(
  'guestsToGuestTags',
  {
    ...XATA_SCHEMA_FIELDS_AND_ID,
    guestId: text('guestId'),
    guestTagId: text('guestTagId'),
  },
  (table) => ({
    ...getXataSchemaIdIndex('guestsToGuestTags', table),
    cpk: primaryKey({ columns: [table.guestId, table.guestTagId] }),
  }),
);

helpers.ts

export const XATA_SCHEMA_FIELDS_AND_ID = {
  id: pgText('id')
    .default(sql`xata_private.xid()`)
    .notNull(),
  xata_id: pgText('xata_id')
    .default(sql`'rec_'::text || (xata_private.xid())::text`)
    .notNull(),
  xata_version: pgInteger('xata_version').default(0).notNull(),
  xata_createdat: pgTimestamp('xata_createdat', {
    withTimezone: true,
    mode: 'string',
  })
    .defaultNow()
    .notNull(),
  xata_updatedat: pgTimestamp('xata_updatedat', {
    withTimezone: true,
    mode: 'string',
  }),
} satisfies Parameters<typeof pgTable>[1];

export const getXataSchemaIdIndex = <TTableName extends string>(
  tableName: TTableName,
  table: BuildColumns<TTableName, typeof XATA_SCHEMA_FIELDS_AND_ID, 'pg'>,
) => {
  return {
    [`_pgroll_new_${tableName}_xata_id_key`]: pgUniqueIndex(
      `_pgroll_new_${tableName}_xata_id_key`,
    ).on(table.xata_id),
  };
};

export const XATA_SCHEMA_FIELDS_AND_ID = {
  id: pgText('id')
    .default(sql`xata_private.xid()`)
    .notNull(),
  xata_id: pgText('xata_id')
    .default(sql`'rec_'::text || (xata_private.xid())::text`)
    .notNull(),
  xata_version: pgInteger('xata_version').default(0).notNull(),
  xata_createdat: pgTimestamp('xata_createdat', {
    withTimezone: true,
    mode: 'string',
  })
    .defaultNow()
    .notNull(),
  xata_updatedat: pgTimestamp('xata_updatedat', {
    withTimezone: true,
    mode: 'string',
  }),
} satisfies Parameters<typeof pgTable>[1];

export const getXataSchemaIdIndex = <TTableName extends string>(
  tableName: TTableName,
  table: BuildColumns<TTableName, typeof XATA_SCHEMA_FIELDS_AND_ID, 'pg'>,
) => {
  return {
    [`_pgroll_new_${tableName}_xata_id_key`]: pgUniqueIndex(
      `_pgroll_new_${tableName}_xata_id_key`,
    ).on(table.xata_id),
  };
};

Note: the columns like pgText are just aliases.

kostas•4mo ago

Thank you, we can reproduce the issue. I am following up with the dev team and will let you know as soon as we have an update. Hi, we've just merged a fix for this issue. Can you try again and let me know if things work this time?

Michael Schaufelberger•3mo ago

Thanks! ~~Hmm, I'm now getting the error: error: column "guestId" is in a primary key~~ FYI I have since changed the above code a bit, so there's no id field inside the XATA_SCHEMA_FIELDS variable. Since that doesn't make sense. Scratch that error above. It seems to be working! 🎉 Drizzle push somehow thinks it needs to remove the not nulls after adding the index. Since I wanted to specify notNull() anyways I added that.

Gaming

Programming

Re-executing Drizzle Kit push fails after successful push