Attempting to change column type throws error

[jonathantjm]

Attempting to change column type throws error

expected one dependency record for TOAST table, found 4

Reproduction:

import { PGlite } from '@electric-sql/pglite'

const db = new PGlite()

await db.exec(`-- CreateEnum

-- CreateTable
CREATE TABLE "my_table" (
    "id" SERIAL NOT NULL,
    "1" VARCHAR(9) NOT NULL,
    "2" VARCHAR(12) NOT NULL,
    "3" VARCHAR(36) NOT NULL,
    "4" BOOLEAN,
    "5" VARCHAR(10),
    "6" VARCHAR(10),
    "7" VARCHAR(7) NOT NULL,
    "8" VARCHAR(7),
    "9" VARCHAR(12),
    "10" VARCHAR(7),
    "11" INTEGER,
    "12" VARCHAR(45) NOT NULL,
    "13" DATE NOT NULL,
    "14" VARCHAR(8) NOT NULL,
    "15" VARCHAR(16) NOT NULL,
    "16" VARCHAR(8) NOT NULL,
    "17" DATE NOT NULL,
    "18" VARCHAR(14),
    "19" VARCHAR(12),
    "20" VARCHAR(8),
    "21" VARCHAR(80),
    "22" VARCHAR(10) NOT NULL,
    "23" VARCHAR(8) NOT NULL,
    "24" VARCHAR(8) NOT NULL,
    "25" VARCHAR(8) NOT NULL,
    "26" VARCHAR(8) NOT NULL,
    "27" VARCHAR(10),
    "28" VARCHAR(15),
    "29" DATE,
    "30" VARCHAR(9),
    "31" VARCHAR(8),
    "32" DECIMAL(5,2) NOT NULL,
    "33" VARCHAR(8),
    "34" VARCHAR(8),
    "35" VARCHAR(20) NOT NULL,
    "36" VARCHAR(8),
    "37" VARCHAR(10) NOT NULL,
    "38" VARCHAR(45),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "my_table_pkey" PRIMARY KEY ("id")
);
`)

await db.exec(`
-- AlterTable
ALTER TABLE "my_table" ALTER COLUMN "created_at" SET DATA TYPE TIMESTAMPTZ(3);
`)

console.log(await db.exec(`SELECT * from "my_table";`))

The following actions individually will cause the code to execute successfully:

1. Removing column 38
2. Reducing the varchar size of column 38 to 10
3. Removing the ALTER COLUMN statement
4. Setting the created_at column as type TIMESTAMPTZ(3) in the CREATE TABLE statement

Edit: formatting

[samwillis]

Hey @jonathantjm, thanks for the report.

This is showing a similar TOAST related error to #63, we need to investigate if there are issues with Postgres TOAST. The fact that making the totable row size slightly smaller tops the problems very much suggests there it is TOAST related (along with the error message).

[barbalex]

I am getting a very similar error when trying to convert my project from using sqlite to pglite:

error when generating label: error: expected one dependency record for TOAST table, found 4

This happens when running this code:

await db.unsafeExec({
  sql: `ALTER TABLE action_reports ADD COLUMN label text GENERATED ALWAYS AS (coalesce(year::text, action_report_id::text)) stored;`,
})

on this table:

CREATE TABLE action_reports(
  action_report_id uuid PRIMARY KEY DEFAULT NULL,
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  action_id uuid DEFAULT NULL REFERENCES actions(action_id) ON DELETE CASCADE ON UPDATE CASCADE,
  year integer DEFAULT NULL,
  data jsonb DEFAULT NULL,
  label_replace_by_generated_column text DEFAULT NULL
);

CREATE INDEX ON action_reports USING btree(account_id);

CREATE INDEX ON action_reports USING btree(action_id);

CREATE INDEX ON action_reports USING btree(year);

This is currently preventing me from using pglite.

[barbalex]

It seems that it doesn't matter what label is generated. The error also occurs on another table:

CREATE TABLE users(
  user_id uuid PRIMARY KEY DEFAULT NULL,
  email text DEFAULT NULL,
  label_replace_by_generated_column text DEFAULT NULL
);

CREATE INDEX ON users USING btree(email);

when running:

await db.unsafeExec({
  sql: `
  ALTER TABLE users ADD COLUMN label text GENERATED ALWAYS AS (coalesce(email::text, user_id::text)) stored;`,
})