A partir da versão 12 (que já tem um tempinho), o Postgres deixou de suportar o uso da coluna oculta “OID” (ou “Object Identifier”), que até então era opcional em tabelas normais e se tornou uma coluna explícita nas tabelas do catálogo do sistema.
Até a versão 11, você podia criar uma tabela com a opção “WITH OIDS“, que criava uma coluna oculta, chamada ‘oid’, que funcionava como uma chave primária (PK) artificial. Desde a versão 8.0 do Postgres (que foi lançada em janeiro de 2005), o parâmetro “default_with_oids” está desligado por padrão, o que significa que se você não disser nada, por padrão, ao criar uma tabela, a coluna oculta oid não será criada. Então, 14 anos depois, o pessoal resolveu aposentar essa opção e finalmente eliminar definitivamente essa coluna de tabelas criada pelo usuário. Mas não é que, vira e mexe, ainda tem gente que usa? Bom, então como lidar com ela? Existem algumas situações diferentes:
- Se você nem sabe que a coluna está lá na tabela ou não a usa para nada e já tem sua própria chave primária (PK), então você só precisa remover a coluna OID com o simples comando abaixo:
ALTER TABLE minha_tabela SET WITHOUT OIDS;- Se você usa essa coluna como uma chave artificial, seria melhor criar uma nova coluna com um nome como “ID” para sua tabela. Isso exige alguns passos, como:
- Criar a coluna ID.
- Ajustar a coluna para que novas linhas recebam um novo número. automaticamente a partir do último número da coluna OID.
- Popular a coluna ID com os valores da coluna OID.
- Alterar a tabela para que a coluna ID se torne uma chave primária (PK).
- Agora, sim, remover a coluna OID.
SELECT coalesce(max(oid)::integer,0) + 1 FROM minha_tabela;
ALTER TABLE minha_tabela ADD COLUMN id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 12345);
UPDATE minha_tabela SET id = OID;
ALTER TABLE minha_tabela ADD PRIMARY KEY (id);
ALTER TABLE minha_tabela SET WITHOUT OIDS;Algumas observações:
- Note que aqui eu peguei o maior valor da coluna OID na primeira consulta e devo utilizá-lo no comando seguinte na cláusula “START WITH”, para dizer qual será o próximo número da sequência.
- O nome ID para a nova coluna é absolutamente arbitrário aqui, você pode utilizar o nome que quiser.
- O tipo de dado para a coluna ID é o INTEGER, mas você pode utilizar o BIGINT se for uma tabela com muitos registros também.
- Eu criei uma “sequence” com a cláusula
GENERATED BY DEFAULT AS IDENTITY. Esse é o padrão, em vez de utilizar o antigoDEFAULT nextval('nome_da_sequence'), desde a versão 10 do Postgres. Tenho um artigo aqui no blog falando especificamente disso, vale a pena conferir, se estiver confuso. - Não é obrigatório ter uma chave primária em toda tabela. Sim, existem raros casos em que é justificável não tê-la, como numa tabela transitória para fazer uma carga de dados. Vamos tratar isso como uma exceção e tocar o barco pensando apenas na regra geral.
Automatizando tarefas
Bom, está na hora de fazer esse negócio andar de forma um pouco mais rápida.
Esses dias, peguei um cliente com centenas de tabelas que ainda utilizam OID. Então, fiz este roteiro aqui para facilitar.
Verificando tabelas com OID
SELECT relnamespace::regnamespace AS table_schema, relname AS table_name
FROM pg_class c
WHERE
relkind IN ('r', 'm', 'p') AND
relhasoids = TRUE AND
relnamespace NOT IN
('pg_catalog'::regnamespace::oid,'information_schema'::regnamespace::oid)
ORDER BY relnamespace::regnamespace, relname;Criando a coluna ID em tabelas sem chave primária (PK)
BEGIN;
DO $$DECLARE
r record;
v_start integer;
BEGIN
FOR r IN SELECT relnamespace::regnamespace AS table_schema, relname AS table_name
FROM pg_class c
WHERE
relkind IN ('r', 'm', 'p') AND
relhasoids = TRUE AND
NOT EXISTS (SELECT 1 FROM pg_constraint WHERE contype = 'p' AND conrelid = c.oid) AND
NOT EXISTS (SELECT 1 FROM pg_attribute a WHERE c.oid = a.attrelid AND attname = 'id') AND
relnamespace NOT IN
('pg_catalog'::regnamespace::oid,'information_schema'::regnamespace::oid)
ORDER BY relnamespace::regnamespace, relname
LOOP
EXECUTE format(
'SELECT coalesce(max(oid)::integer,0) + 1
FROM %I.%I',r.table_schema, r.table_name)
INTO v_start;
RAISE WARNING 'Creating identity column for table %.% starting with %',
r.table_schema, r.table_name, v_start;
EXECUTE format(
'ALTER TABLE %I.%I ADD COLUMN id integer
GENERATED BY DEFAULT AS IDENTITY
(START WITH ' || v_start || ')',r.table_schema, r.table_name);
RAISE WARNING 'UPDATE id';
EXECUTE format('UPDATE %I.%I SET id = oid',r.table_schema, r.table_name);
RAISE WARNING 'CREATE PK';
EXECUTE format('ALTER TABLE %I.%I ADD PRIMARY KEY(id)',r.table_schema, r.table_name);
RAISE WARNING 'REMOVE oid';
EXECUTE format('ALTER TABLE %I.%I SET WITHOUT OIDS',r.table_schema, r.table_name);
END LOOP;
END$$;
COMMIT;Removendo a coluna OID em tabelas com chave primária (PK)
BEGIN;
SELECT 'ALTER TABLE ' || relnamespace::regnamespace || '.' || relname || ' SET WITHOUT OIDS; --' command
FROM pg_class c
WHERE
relkind IN ('r', 'm', 'p') AND
relhasoids = TRUE AND
EXISTS (SELECT 1 FROM pg_constraint WHERE contype = 'p' AND conrelid = c.oid) AND
relnamespace NOT IN
('pg_catalog'::regnamespace::oid,'information_schema'::regnamespace::oid)
ORDER BY relnamespace::regnamespace, relname
\gexec Por enquanto é isso. Espero que você não se depare com esse tipo de situação no cotidiano, mas, se acontecer, este artigo deve quebrar um galho!
Deixe um comentário