Как поменять значения во всех таблицах postgresql?

Question

[BaJIepaMoTo6JIok]

Столкнулся с проблемой при обновлении постгреса с 10 до 14 через pg_upgrade.

Your installation contains tables declared WITH OIDS

Он в файлик вывел список таблиц в различных базах. Как можно выполнить команду ALTER TABLE ... SET WITHOUT OID сразу в нескольких таблицах в разных базах?

Answer 1

[BaJIepaMoTo6JIok]

В общем решение такое. В каждой бд прогоняется данный скрипт

SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET WITHOUT OIDS;'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE 1=1
  AND c.relkind = 'r'
  AND c.relhasoids = true
  AND n.nspname <> 'pg_catalog' 
order by n.nspname, c.relname;

результатом выполнения которого будет пачка запросов в выводе, которые просто можно целиком скопировать и прогнать

Answer 2

[Сергей Соловьев]

Нашел ответ тут - https://fraserclark.net/2023-10-06-rds-oids/
Вот скрипт, который он предложил:

#!/bin/bash

# I found without setting this the script would fail
export PGPASSWORD=xxxx

# Define your query
QUERY="SELECT current_database(),n.nspname ,c.relname, c.relkind from pg_class c, pg_attribute a, pg_namespace n where c.oid = a.attrelid and a.attname = 'oid' AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema') and c.relkind = 'r';"

# Get a list of all databases in the RDS cluster
DATABASES=$(psql -h hostname.eu-west-2.rds.amazonaws.com -p 5432 -U postgres -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1');" -t)

# Loop through each database and execute the query
for DB in $DATABASES; do
    echo "Running query on database: $DB"
    psql -h qa-hostname.eu-west-2.rds.amazonaws.com -t -c "$QUERY" -U postgres -d $DB
done

Comments

[BaJIepaMoTo6JIok]

Судя по описанию этот скрипт лишь выведет список таблиц, где необходимо отключить OIDS

This will output a list of databases and tables that need to be updated. You can then run the following query to remove the OIDS:

ALTER TABLE table_name SET WITHOUT OIDS;

[Сергей Соловьев]

BaJIepaMoTo6JIok, нашел еще один скрипт

Explain
    SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET WITHOUT OIDS;'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE 1=1
  AND c.relkind = 'r'
  AND c.relhasoids = true
  AND n.nspname <> 'pg_catalog' 
order by n.nspname, c.relname;

[Сергей Соловьев]

BaJIepaMoTo6JIok, еще вот тут человек говорит, что создал отдельный скрипт для этого - https://dba.stackexchange.com/a/260282

[BaJIepaMoTo6JIok]

Сергей Соловьев, предпоследний скрипт оказался верным

Answer 3

[ky0]

Поскольку это операция разовая - просто возьмите список таблиц и выполните в цикле ALTER TABLE.