tests/bundle-list-index-migration.test.ts
Rama main · réplica de lectura del repositorio original.
import type { PoolClient } from 'pg'
import { randomUUID } from 'node:crypto'
import { readFileSync } from 'node:fs'
import { Client, Pool } from 'pg'
import { afterAll, describe, expect, it } from 'vitest'
import { POSTGRES_URL } from './test-utils.ts'
const migration = readFileSync(new URL('../supabase/migrations/20261029025503_bundle_list_index.sql', import.meta.url), 'utf8')
const indexName = 'idx_app_versions_app_deleted_created_at'
const pool = new Pool({ connectionString: POSTGRES_URL, max: 2 })
/** Replay the actual migration against a private schema with no shared fixtures. */
async function withIndexFixture(run: (client: PoolClient, schema: string, sql: string) => Promise<void>) {
const client = await pool.connect()
const schema = `bundle_list_index_${randomUUID().replaceAll('-', '')}`
const sql = migration.replaceAll(`public.${indexName}`, `${schema}.${indexName}`)
.replaceAll('ON public.app_versions', `ON ${schema}.versions`)
try {
await client.query(`CREATE SCHEMA ${schema}`)
await client.query(`CREATE TABLE ${schema}.versions (app_id varchar, deleted boolean, created_at timestamptz)`)
await run(client, schema, sql)
}
finally {
try {
await client.query('ROLLBACK')
await client.query(`DROP SCHEMA IF EXISTS ${schema} CASCADE`)
}
finally {
client.release()
}
}
}
/** Read the index identity and usability flags without modifying the catalogs. */
async function indexState(client: PoolClient, schema: string) {
const result = await client.query(`SELECT indexrelid, indisvalid, indisready FROM pg_catalog.pg_index
WHERE indexrelid=pg_catalog.to_regclass($1)`, [`${schema}.${indexName}`])
return result.rows[0] as { indexrelid: number, indisvalid: boolean, indisready: boolean } | undefined
}
afterAll(async () => {
await pool.end()
})
describe('bundle-list index migration preflight', () => {
it.concurrent('creates a missing index and reuses it on repeated application', async () => {
await withIndexFixture(async (client, schema, sql) => {
await client.query('BEGIN')
expect(await indexState(client, schema)).toBeUndefined()
await client.query(sql)
const created = await indexState(client, schema)
expect(created).toMatchObject({ indisvalid: true, indisready: true })
await client.query(sql)
expect(await indexState(client, schema)).toEqual(created)
})
})
it.concurrent('reuses a valid index built concurrently before the migration', async () => {
await withIndexFixture(async (client, schema, sql) => {
await client.query(`CREATE INDEX CONCURRENTLY ${indexName} ON ${schema}.versions (app_id, deleted, created_at DESC)`)
const prebuilt = await indexState(client, schema)
expect(prebuilt).toMatchObject({ indisvalid: true, indisready: true })
await client.query('BEGIN')
await client.query(sql)
expect(await indexState(client, schema)).toEqual(prebuilt)
})
})
it.concurrent.each([
{ kind: 'table', definition: '(id integer)' },
{ kind: 'view', definition: 'AS SELECT 1 AS id' },
{ kind: 'sequence', definition: '' },
{ kind: 'materialized view', definition: 'AS SELECT 1 AS id' },
])('rejects a same-name $kind without changing it', async ({ kind, definition }) => {
await withIndexFixture(async (client, schema, sql) => {
await client.query(`CREATE ${kind.toUpperCase()} ${schema}.${indexName} ${definition}`)
const stateQuery = 'SELECT oid, relkind FROM pg_catalog.pg_class WHERE oid=pg_catalog.to_regclass($1)'
const stateParams = [`${schema}.${indexName}`]
const original = await client.query(stateQuery, stateParams)
expect(original.rows).toHaveLength(1)
expect(await indexState(client, schema)).toBeUndefined()
await client.query('BEGIN')
await expect(client.query(sql)).rejects.toMatchObject({
code: 'P0001',
message: `${indexName} exists but is not an index; inspect it before retrying`,
})
await client.query('ROLLBACK')
expect((await client.query(stateQuery, stateParams)).rows).toEqual(original.rows)
await client.query(`DROP ${kind.toUpperCase()} ${schema}.${indexName}`)
await client.query('BEGIN')
await client.query(sql)
expect(await indexState(client, schema)).toMatchObject({ indisvalid: true, indisready: true })
})
})
it.concurrent.each([
{ name: 'missing key', definition: '(app_id, created_at DESC)' },
{ name: 'wrong key order', definition: '(deleted, app_id, created_at DESC)' },
{ name: 'ascending time order', definition: '(app_id, deleted, created_at ASC)' },
{ name: 'different null ordering', definition: '(app_id, deleted, created_at DESC NULLS LAST)' },
{ name: 'partial predicate', definition: '(app_id, deleted, created_at DESC) WHERE deleted = false' },
{ name: 'expression key', definition: '(lower(app_id), deleted, created_at DESC)' },
{ name: 'unique index', definition: '(app_id, deleted, created_at DESC)', unique: true },
{ name: 'wrong table', definition: '(app_id, deleted, created_at DESC)', otherTable: true },
])('rejects a valid same-name index with $name', async ({ definition, unique, otherTable }) => {
await withIndexFixture(async (client, schema, sql) => {
const table = otherTable ? 'other_versions' : 'versions'
if (otherTable)
await client.query(`CREATE TABLE ${schema}.other_versions (LIKE ${schema}.versions)`)
await client.query(`CREATE ${unique ? 'UNIQUE ' : ''}INDEX ${indexName} ON ${schema}.${table} ${definition}`)
const mismatched = await indexState(client, schema)
expect(mismatched).toMatchObject({ indisvalid: true, indisready: true })
await client.query('BEGIN')
await expect(client.query(sql)).rejects.toMatchObject({
code: 'P0001',
message: `${indexName} exists with an unexpected definition; inspect it before retrying`,
})
await client.query('ROLLBACK')
expect(await indexState(client, schema)).toEqual(mismatched)
await client.query(`DROP INDEX CONCURRENTLY ${schema}.${indexName}`)
await client.query('BEGIN')
await client.query(sql)
expect(await indexState(client, schema)).toMatchObject({ indisvalid: true, indisready: true })
})
})
it.concurrent.each([
{
name: 'failed concurrent prebuild',
create: (schema: string) => `CREATE UNIQUE INDEX CONCURRENTLY ${indexName} ON ${schema}.versions (app_id)`,
expectedError: '23505',
accepted: false,
},
{
name: 'mismatched concurrent prebuild',
create: (schema: string) => `CREATE INDEX CONCURRENTLY ${indexName} ON ${schema}.versions (app_id)`,
accepted: false,
},
{
name: 'same-name sequence',
create: (schema: string) => `CREATE SEQUENCE ${schema}.${indexName}`,
accepted: false,
},
{
name: 'matching concurrent prebuild',
create: (schema: string) => `CREATE INDEX CONCURRENTLY ${indexName} ON ${schema}.versions (app_id, deleted, created_at DESC)`,
accepted: true,
},
])('validates a $name introduced after preflight', async ({ create, expectedError, accepted }) => {
await withIndexFixture(async (client, schema, sql) => {
const builder = new Client({ connectionString: POSTGRES_URL, statement_timeout: 5000 })
try {
await builder.connect()
await client.query(`INSERT INTO ${schema}.versions(app_id) VALUES ('duplicate'), ('duplicate')`)
const createStart = sql.indexOf('\nCREATE INDEX IF NOT EXISTS ')
expect(createStart).toBeGreaterThan(0)
await client.query('BEGIN')
await client.query(sql.slice(0, createStart))
const build = builder.query(create(schema))
if (expectedError)
await expect(build).rejects.toMatchObject({ code: expectedError })
else
await build
if (expectedError)
expect(await indexState(client, schema)).toMatchObject({ indisvalid: false })
const stateQuery = `SELECT rel.oid, rel.relkind, idx.indisvalid, idx.indisready
FROM pg_catalog.pg_class AS rel
LEFT JOIN pg_catalog.pg_index AS idx ON idx.indexrelid = rel.oid
WHERE rel.oid = pg_catalog.to_regclass($1)`
const stateParams = [`${schema}.${indexName}`]
const original = (await client.query(stateQuery, stateParams)).rows
expect(original).toHaveLength(1)
if (accepted) {
await client.query(sql.slice(createStart))
expect(await indexState(client, schema)).toMatchObject({ indisvalid: true, indisready: true })
}
else {
await expect(client.query(sql.slice(createStart))).rejects.toMatchObject({
code: 'P0001',
message: `${indexName} failed post-build validation; inspect it before retrying`,
})
}
await client.query('ROLLBACK')
expect((await client.query(stateQuery, stateParams)).rows).toEqual(original)
}
finally {
await builder.end()
}
})
})
it.concurrent('rejects a failed concurrent prebuild until it is removed and retried', async () => {
await withIndexFixture(async (client, schema, sql) => {
await client.query(`INSERT INTO ${schema}.versions(app_id) VALUES ('duplicate'), ('duplicate')`)
await expect(client.query(`CREATE UNIQUE INDEX CONCURRENTLY ${indexName} ON ${schema}.versions (app_id)`))
.rejects.toMatchObject({ code: '23505' })
const invalid = await indexState(client, schema)
expect(invalid).toMatchObject({ indisvalid: false })
await client.query('BEGIN')
await expect(client.query(sql)).rejects.toMatchObject({
code: 'P0001',
message: `${indexName} exists but is invalid or not ready; drop it concurrently and retry`,
})
await client.query('ROLLBACK')
expect(await indexState(client, schema)).toEqual(invalid)
await client.query(`DROP INDEX CONCURRENTLY ${schema}.${indexName}`)
await client.query('BEGIN')
await client.query(sql)
expect(await indexState(client, schema)).toMatchObject({ indisvalid: true, indisready: true })
})
})
})