英文:
Increment all sequences in Postgresql DB
问题
我正在使用AWS DMS迁移一个PostgreSQL数据库。源数据库位于Aurora Postgres 10.xx上，目标数据库位于Aurora Postgres 12.xx上。
由于DMS不同步序列，所以我正在使用以下查询（在目标数据库上）来创建动态SQL查询，这些查询将通过与关联列的最大值+1000增加我的序列：
SELECT
'SELECT SETVAL(' ||
quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1)+ 1000 ) FROM ' || -- 缓冲1000
quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend
INNER JOIN pg_class AS class_sequence
ON class_sequence.oid = pg_depend.objid
AND class_sequence.relkind = 'S'
INNER JOIN pg_class AS class_table
ON class_table.oid = pg_depend.refobjid
INNER JOIN pg_attribute
ON pg_attribute.attrelid = class_table.oid
AND pg_depend.refobjsubid = pg_attribute.attnum
INNER JOIN pg_namespace as table_namespace
ON table_namespace.oid = class_table.relnamespace
INNER JOIN pg_namespace AS sequence_namespace
ON sequence_namespace.oid = class_sequence.relnamespace
where sequence_namespace.nspname='public' -- 这是模式名称
ORDER BY sequence_namespace.nspname, class_sequence.relname;
这将返回总共15个SQL语句，这意味着在public模式中总共有15个序列。
现在，当我在同一个数据库上执行\ds public.*时，它给我返回25个序列。我无法弄清楚为什么SELECT查询无法选择所有25个序列。
有人可以帮忙吗？
谢谢！
英文:
I migrating a PostgreSQL DB using AWS DMS. The source DB is on Aurora Postgres 10.xx and the target DB is on Aurora Postgres 12.xx.
Since DMS does not sync sequences, so I am using the following query (on the target DB) to create Dynamic sql queries which will increment my sequences by max_value of the associated column + 1000:
SELECT
'SELECT SETVAL(' ||
quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1)+ 1000 ) FROM ' || -- Buffer of 1000
quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend
INNER JOIN pg_class AS class_sequence
ON class_sequence.oid = pg_depend.objid
AND class_sequence.relkind = 'S'
INNER JOIN pg_class AS class_table
ON class_table.oid = pg_depend.refobjid
INNER JOIN pg_attribute
ON pg_attribute.attrelid = class_table.oid
AND pg_depend.refobjsubid = pg_attribute.attnum
INNER JOIN pg_namespace as table_namespace
ON table_namespace.oid = class_table.relnamespace
INNER JOIN pg_namespace AS sequence_namespace
ON sequence_namespace.oid = class_sequence.relnamespace
where sequence_namespace.nspname='public' -- This is the schema name
ORDER BY sequence_namespace.nspname, class_sequence.relname;
This returns a total of 15 SQL statements which means 15 sequences in total in the public schema.
Now when I do a \ds public.* on the same database, it gives me 25 sequences. I am not able to figure out why the SELECT query is not able to pick up all the 25 sequences.
Can someone please help here?
Thanks
答案1
得分: 1
只返回翻译好的部分：
你的查询仅返回由表列拥有的序列：
ALTER SEQUENCE seq OWNED BY tab.id;
其他序列似乎没有与表列的这种关联。
英文:
Your query only returns sequences that are owned by a table column:
ALTER SEQUENCE seq OWNED BY tab.id;
The other sequences seem not to have such a relationship to a table column.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论