Last active
November 1, 2022 10:28
-
-
Save capaj/080b533f404602ba1283de9aaaecbc12 to your computer and use it in GitHub Desktop.
can be used to fix the sequences after migrating postgre using AWS DMS for example
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import 'dotenv/config' | |
import { prismaClient } from '../src/lib/prismaClient' | |
const setSequenceToMaxInTable = async (props: { | |
table_name: string | |
sequence_name: string | |
column_name: string | |
}) => { | |
const setval = ` | |
SELECT setval('"${props.sequence_name}"', COALESCE((SELECT MAX(${props.column_name})+1 FROM ${props.table_name}), 1), false); | |
` | |
const [ret] = (await prismaClient.$queryRawUnsafe(setval)) as { | |
setval: number | |
}[] | |
console.log(`set max for sequence ${props.sequence_name} to ${ret.setval}`) | |
// const setOwner = `ALTER SEQUENCE "${props.sequence_name}" OWNER TO official_prod_api;` | |
// await prismaClient.$queryRawUnsafe(setOwner) | |
// console.log(setOwner) | |
} | |
/** | |
* this is used for fixing the sequences after a restore from a backup or after migrating to a new database | |
*/ | |
const setAllSequences = async () => { | |
const sequences = | |
(await prismaClient.$queryRawUnsafe(`SELECT t.oid::regclass::text AS table_name, | |
a.attname AS column_name, | |
s.relname AS sequence_name, | |
d.deptype, t.relkind, s.relkind | |
FROM pg_class AS t | |
JOIN pg_attribute AS a | |
ON a.attrelid = t.oid | |
JOIN pg_depend AS d | |
ON d.refobjid = t.oid | |
AND d.refobjsubid = a.attnum | |
JOIN pg_class AS s | |
ON s.oid = d.objid | |
WHERE d.classid = 'pg_catalog.pg_class'::regclass | |
AND d.refclassid = 'pg_catalog.pg_class'::regclass | |
AND d.deptype IN ('a', 'i') | |
AND t.relkind IN ('r', 'P') | |
AND s.relkind = 'S';`)) as { | |
table_name: string | |
sequence_name: string | |
column_name: string | |
}[] | |
console.log('~ sequences', sequences) | |
for (const sequence of sequences) { | |
if (!sequence.table_name.includes('public')) { | |
await setSequenceToMaxInTable(sequence) | |
} | |
} | |
} | |
setAllSequences().then(() => { | |
process.exit(0) | |
}) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment